/****** Object:  Table [dbo].[capabilities]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[capabilities](
	[capability_id] [int] IDENTITY(1,1) NOT NULL,
	[capability_nm] [nvarchar](250) NOT NULL,
	[capability_desc] [nvarchar](1000) NULL,
	[capability_ntext] [ntext] NULL,
	[capability_cdl] [ntext] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_capabilites] PRIMARY KEY CLUSTERED 
(
	[capability_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[datatypes]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[datatypes](
	[datatype] [nvarchar](100) NOT NULL,
	[datatype_nm] [nvarchar](250) NOT NULL,
	[datatype_desc] [nvarchar](1000) NULL,
	[datatype_dotnet] [nvarchar](250) NULL,
 CONSTRAINT [PK_datatypes] PRIMARY KEY CLUSTERED 
(
	[datatype] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[activity_status]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activity_status](
	[status_id] [int] NOT NULL,
	[status_nm] [nvarchar](250) NOT NULL,
	[status_desc] [nvarchar](1000) NULL,
 CONSTRAINT [PK_activity_status] PRIMARY KEY CLUSTERED 
(
	[status_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[activity_log_types]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activity_log_types](
	[activity_log_type_id] [int] NOT NULL,
	[activity_log_type_nm] [nvarchar](250) NOT NULL,
	[activity_log_type_desc] [nvarchar](1000) NULL,
 CONSTRAINT [PK_activity_log_types] PRIMARY KEY CLUSTERED 
(
	[activity_log_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[business_policies]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[business_policies](
	[bpolicy_id] [int] IDENTITY(1,1) NOT NULL,
	[bpolicy_nm] [nvarchar](250) NOT NULL,
	[bpolicy_desc] [nvarchar](1000) NULL,
	[bpolicy_ntext] [ntext] NULL,
	[bpolicy_manager] [nvarchar](250) NULL,
	[bpolicy_rule] [ntext] NULL,
	[bpolicy_ruledesc] [ntext] NULL,
	[bpolicy_action] [ntext] NULL,
	[bpolicy_actiondesc] [ntext] NULL,
	[bpolicy_code] [ntext] NULL,
	[bpolicy_resultstrings] [ntext] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_business_policies] PRIMARY KEY CLUSTERED 
(
	[bpolicy_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[business_objects]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[business_objects](
	[bobj_id] [int] IDENTITY(1,1) NOT NULL,
	[bobj_nm] [nvarchar](250) NOT NULL,
	[bobj_desc] [nvarchar](1000) NULL,
	[bobj_ntext] [ntext] NULL,
	[bobj_schema] [ntext] NULL,
	[bobj_fields] [ntext] NULL,
	[wf_field_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_domain_business_objects] PRIMARY KEY CLUSTERED 
(
	[bobj_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[logical_method_groups]    Script Date: 04/17/2009 14:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logical_method_groups](
	[lm_group_id] [int] IDENTITY(1,1) NOT NULL,
	[lm_group_nm] [nvarchar](250) NOT NULL,
	[lm_group_desc] [nvarchar](1000) NULL,
	[lm_group_ntext] [ntext] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
 CONSTRAINT [PK_logical_method_groups] PRIMARY KEY CLUSTERED 
(
	[lm_group_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[language]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[language](
	[lang_id] [int] IDENTITY(1,1) NOT NULL,
	[lang_nm] [nvarchar](60) NOT NULL,
 CONSTRAINT [XPKlanguage] PRIMARY KEY CLUSTERED 
(
	[lang_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[get_activities]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activities] 
	-- Add the parameters for the stored procedure here
	@workflow_instance_id int = NULL,
	@user_id int = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @workflow_instance_id, @user_id
END
GO
/****** Object:  Table [dbo].[generic_folder]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[generic_folder](
	[folder_id] [int] IDENTITY(1,1) NOT NULL,
	[folder_nm] [nvarchar](250) NOT NULL,
	[folder_desc] [nvarchar](1000) NULL,
	[folder_ntext] [ntext] NULL,
	[folder_types] [nvarchar](2000) NULL,
	[folder_properties] [ntext] NULL,
	[is_user_folder] [bit] NOT NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
 CONSTRAINT [PK_generic_folder] PRIMARY KEY CLUSTERED 
(
	[folder_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[configurations]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[configurations](
	[config_guid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[config_key] [nvarchar](255) NOT NULL,
	[config_value] [ntext] NULL,
	[config_type] [nvarchar](255) NULL,
	[config_desc] [ntext] NULL,
	[parent_config_guid] [uniqueidentifier] NULL,
	[config_path] [nvarchar](2048) NULL,
	[config_isvalid] [bit] NULL,
	[config_isdeleted] [bit] NULL,
	[config_lastsaved] [datetime] NULL,
	[config_lasteditor] [nvarchar](255) NULL,
 CONSTRAINT [PK_configurations] PRIMARY KEY CLUSTERED 
(
	[config_guid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[documentations]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[documentations](
	[doc_id] [int] IDENTITY(1,1) NOT NULL,
	[doc_nm] [nvarchar](250) NOT NULL,
	[doc_desc] [nvarchar](1000) NULL,
	[doc_ntext] [ntext] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
 CONSTRAINT [PK_documentations] PRIMARY KEY CLUSTERED 
(
	[doc_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[document_schemas]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[document_schemas](
	[doc_schema_guid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[doc_schema_name] [nvarchar](255) NOT NULL,
	[doc_schema] [text] NOT NULL,
	[is_canonical] [bit] NOT NULL,
	[doc_schema_namespace] [nvarchar](2048) NULL,
	[is_simple_type] [bit] NOT NULL,
 CONSTRAINT [PK_document_schemas] PRIMARY KEY CLUSTERED 
(
	[doc_schema_guid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[domains]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[domains](
	[domain_id] [int] IDENTITY(1,1) NOT NULL,
	[domain_nm] [nvarchar](250) NOT NULL,
	[domain_desc] [nvarchar](1000) NULL,
	[domain_type] [int] NULL,
	[domain_ntext] [ntext] NULL,
	[domain_schema] [ntext] NULL,
	[domain_properties] [xml] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[parent_domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_domains] PRIMARY KEY CLUSTERED 
(
	[domain_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[installation_options]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[installation_options](
	[web_addr] [nvarchar](120) NOT NULL,
	[to_email_addr] [nvarchar](60) NOT NULL,
	[use_language_dictionary_ind] [bit] NULL,
	[web_file_location] [nvarchar](2000) NULL,
	[field_01] [binary](100) NULL,
	[version] [nvarchar](60) NOT NULL,
	[tz_id] [int] NULL,
	[daylight_savings_ind] [bit] NOT NULL,
	[org_id] [int] NULL,
	[is_in_maintain_mode] [bit] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[humanactivity_outputdata]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[humanactivity_outputdata](
	[activity_id] [int] NOT NULL,
	[data] [ntext] NULL,
	[soap_envelope] [ntext] NULL,
	[date_created] [datetime] NULL,
	[date_changed] [datetime] NULL,
 CONSTRAINT [PK_humanactivity_outputdata] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[humanactivity_instances]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[humanactivity_instances](
	[activity_id] [int] NOT NULL,
	[hact_nm] [nvarchar](250) NULL,
	[reply_uri] [nvarchar](2000) NULL,
	[message_id] [nvarchar](100) NULL,
	[date_created] [datetime] NULL,
 CONSTRAINT [PK_humanactivity_instances] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[humanactivity_inputdata]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[humanactivity_inputdata](
	[activity_id] [int] NOT NULL,
	[data] [ntext] NULL,
	[soap_envelope] [ntext] NULL,
	[date_created] [datetime] NULL,
	[date_changed] [datetime] NULL,
 CONSTRAINT [PK_humanactivity_inputdata] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[humanactivity_formdata]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[humanactivity_formdata](
	[activity_id] [int] NOT NULL,
	[data] [ntext] NULL,
	[soap_envelope] [ntext] NULL,
	[date_created] [datetime] NULL,
	[date_changed] [datetime] NULL,
 CONSTRAINT [PK_humanactivity_formdata] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[repository_business_rules]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[repository_business_rules](
	[brule_id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](256) NULL,
	[org_id] [int] NULL,
	[brule_nm] [nvarchar](250) NOT NULL,
	[brule_desc] [nvarchar](1000) NULL,
	[brule_ntext] [ntext] NULL,
	[brule_code] [ntext] NULL,
	[brule_parameters] [ntext] NULL,
	[brule_results] [ntext] NULL,
	[brule_usedbrules] [nvarchar](2000) NULL,
	[brule_xml] [ntext] NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_repository_business_rules] PRIMARY KEY CLUSTERED 
(
	[brule_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[repositories]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[repositories](
	[repository_id] [int] IDENTITY(1,1) NOT NULL,
	[repository_nm] [nvarchar](250) NOT NULL,
	[repository_desc] [nvarchar](1000) NULL,
	[repository_ntext] [ntext] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NULL,
	[org_unit_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
 CONSTRAINT [PK_repositories] PRIMARY KEY CLUSTERED 
(
	[repository_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[participation_types]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[participation_types](
	[participation_type_id] [int] NOT NULL,
	[participation_type_nm] [nvarchar](250) NOT NULL,
	[participation_type_desc] [nvarchar](1000) NULL,
 CONSTRAINT [PK_participation_types] PRIMARY KEY CLUSTERED 
(
	[participation_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[repository_types]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[repository_types](
	[reptype_id] [int] NOT NULL,
	[reptype_enum_nm] [nvarchar](1000) NULL,
	[reptype_enum_nm_alt] [nvarchar](1000) NULL,
	[reptype_icon_16] [image] NULL,
	[reptype_selected_icon_16] [image] NULL,
	[reptype_display] [nvarchar](250) NULL,
 CONSTRAINT [PK_domaintree_folder_types] PRIMARY KEY CLUSTERED 
(
	[reptype_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Alternative enumeration name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'repository_types'
GO
/****** Object:  Table [dbo].[workflow_instances]    Script Date: 04/17/2009 14:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[workflow_instances](
	[workflow_instance_id] [uniqueidentifier] NOT NULL,
	[process_nm] [nvarchar](250) NOT NULL,
	[created_dt] [datetime] NULL,
 CONSTRAINT [PK_workflow_instances] PRIMARY KEY CLUSTERED 
(
	[workflow_instance_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[wf_dlls]    Script Date: 04/17/2009 14:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wf_dlls](
	[wf_dll_id] [int] IDENTITY(1,1) NOT NULL,
	[wf_dll_fullname] [nvarchar](1000) NULL,
	[wf_dll_data] [image] NULL,
 CONSTRAINT [PK_wf_dlls] PRIMARY KEY CLUSTERED 
(
	[wf_dll_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[webservices]    Script Date: 04/17/2009 14:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[webservices](
	[websvc_id] [int] IDENTITY(1,1) NOT NULL,
	[websvc_nm] [nvarchar](250) NOT NULL,
	[websvc_desc] [nvarchar](1000) NULL,
	[websvc_type] [int] NULL,
	[websvc_uri] [nvarchar](2000) NULL,
	[websvc_ntext] [ntext] NULL,
	[websvc_wsdl] [ntext] NULL,
	[websvc_xmlns] [nvarchar](2000) NULL,
	[websvc_contractcs] [ntext] NULL,
	[websvc_metadata] [ntext] NULL,
	[websvc_clientfullname] [nvarchar](1000) NULL,
	[websvc_interfacefullname] [nvarchar](1000) NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_webservices] PRIMARY KEY CLUSTERED 
(
	[websvc_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[webmethods]    Script Date: 04/17/2009 14:02:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[webmethods](
	[webmethod_id] [int] IDENTITY(1,1) NOT NULL,
	[webmethod_nm] [nvarchar](250) NOT NULL,
	[webmethod_desc] [nvarchar](1000) NULL,
	[webmethod_action] [nvarchar](2000) NULL,
	[webmethod_porttype] [nvarchar](2000) NULL,
	[webmethod_ntext] [ntext] NULL,
	[webmethod_inputwrapper] [nvarchar](500) NULL,
	[webmethod_inputtypenm] [nvarchar](500) NULL,
	[webmethod_inputvarnm] [nvarchar](500) NULL,
	[webmethod_inputaction] [nvarchar](2000) NULL,
	[webmethod_inputschema] [ntext] NULL,
	[webmethod_outputwrapper] [nvarchar](500) NULL,
	[webmethod_outputtypenm] [nvarchar](500) NULL,
	[webmethod_outputvarnm] [nvarchar](500) NULL,
	[webmethod_outputaction] [nvarchar](2000) NULL,
	[webmethod_outputschema] [ntext] NULL,
	[webmethod_xmlns] [nvarchar](2000) NULL,
	[websvc_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_webmethods] PRIMARY KEY CLUSTERED 
(
	[webmethod_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[set_activity_outputdata]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-17
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[set_activity_outputdata] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int,
	@data ntext,
	@soap_envelope ntext
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF EXISTS ( SELECT * FROM humanactivity_outputdata WHERE activity_id = @activity_id )
	BEGIN
		-- update output data
		UPDATE humanactivity_outputdata
		SET data = @data, soap_envelope = @soap_envelope, date_changed = GETDATE()
		WHERE activity_id = @activity_id
	END ELSE BEGIN
		-- insert new output data
		INSERT INTO humanactivity_outputdata
			( activity_id, data, soap_envelope, date_created, date_changed )
		VALUES
			( @activity_id, @data, @soap_envelope, GETDATE(), NULL )
	END
END
GO
/****** Object:  StoredProcedure [dbo].[set_activity_inputdata]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[set_activity_inputdata] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int,
	@data ntext,
	@soap_envelope ntext
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF EXISTS ( SELECT * FROM humanactivity_inputdata WHERE activity_id = @activity_id )
	BEGIN
		-- update input data
		UPDATE humanactivity_inputdata
		SET data = @data, soap_envelope = @soap_envelope, date_changed = GETDATE()
		WHERE activity_id = @activity_id
	END ELSE BEGIN
		-- insert new input data
		INSERT INTO humanactivity_inputdata
			( activity_id, data, soap_envelope, date_created, date_changed )
		VALUES
			( @activity_id, @data, @soap_envelope, GETDATE(), NULL )
	END
END
GO
/****** Object:  StoredProcedure [dbo].[set_activity_formdata]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[set_activity_formdata] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int,
	@data ntext,
	@soap_envelope ntext
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF EXISTS ( SELECT * FROM humanactivity_formdata WHERE activity_id = @activity_id )
	BEGIN
		-- update formdata
		UPDATE humanactivity_formdata
		SET data = @data, soap_envelope = @soap_envelope, date_changed = GETDATE()
		WHERE activity_id = @activity_id
	END ELSE BEGIN
		-- insert new formdata
		INSERT INTO humanactivity_formdata
			( activity_id, data, soap_envelope, date_created, date_changed )
		VALUES
			( @activity_id, @data, @soap_envelope, GETDATE(), NULL )
	END

END
GO
/****** Object:  Table [dbo].[repository_hierarchy]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[repository_hierarchy](
	[parent_reptype_id] [int] NULL,
	[parent_id] [int] NULL,
	[parent_display] [nvarchar](250) NULL,
	[parent_image_key] [nvarchar](100) NULL,
	[parent_selected_image_key] [nvarchar](100) NULL,
	[child_reptype_id] [int] NOT NULL,
	[child_id] [int] NOT NULL,
	[child_display] [nvarchar](250) NULL,
	[child_image_key] [nvarchar](100) NULL,
	[child_selected_image_key] [nvarchar](100) NULL,
	[repository_id] [int] NOT NULL,
	[position] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_repository_hierarchy] ON [dbo].[repository_hierarchy] 
(
	[parent_id] ASC,
	[position] ASC,
	[child_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[repository_diagrams]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[repository_diagrams](
	[diagram_id] [int] IDENTITY(1,1) NOT NULL,
	[diagram_nm] [nvarchar](250) NOT NULL,
	[diagram_desc] [nvarchar](1000) NULL,
	[diagram_ntext] [ntext] NULL,
	[diagram_type] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_repository_diagrams] PRIMARY KEY CLUSTERED 
(
	[diagram_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[rule_constants]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[rule_constants](
	[ruleconst_id] [int] IDENTITY(1,1) NOT NULL,
	[ruleconst_nm] [nvarchar](250) NOT NULL,
	[ruleconst_desc] [nvarchar](1000) NULL,
	[ruleconst_ntext] [ntext] NULL,
	[ruleconst_value] [nvarchar](2000) NULL,
	[ruleconst_datatype] [nvarchar](100) NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_rule_constants] PRIMARY KEY CLUSTERED 
(
	[ruleconst_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[organization]    Script Date: 04/17/2009 14:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[organization](
	[org_id] [int] IDENTITY(1,1) NOT NULL,
	[org_nm] [nvarchar](60) NOT NULL,
	[lang_id] [int] NOT NULL,
	[master_org_ind] [bit] NULL,
	[can_update_ind] [bit] NULL,
	[org_desc] [nvarchar](1000) NULL,
	[org_ntext] [ntext] NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
 CONSTRAINT [XPKpwc] PRIMARY KEY CLUSTERED 
(
	[org_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [XAK1organization_nm] UNIQUE NONCLUSTERED 
(
	[org_nm] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[nouns]    Script Date: 04/17/2009 14:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[nouns](
	[noun_id] [int] IDENTITY(1,1) NOT NULL,
	[noun_nm] [nvarchar](250) NOT NULL,
	[noun_desc] [nvarchar](1000) NULL,
	[noun_ntext] [ntext] NULL,
	[noun_type] [int] NULL,
	[noun_datatype] [nvarchar](100) NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_domain_vocabulary] PRIMARY KEY CLUSTERED 
(
	[noun_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[human_activities]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[human_activities](
	[hact_id] [int] IDENTITY(1,1) NOT NULL,
	[hact_nm] [nvarchar](250) NOT NULL,
	[hact_desc] [nvarchar](1000) NULL,
	[hact_ntext] [ntext] NULL,
	[hact_form_dll] [image] NULL,
	[hact_form_asm_nm] [nvarchar](250) NULL,
	[hact_assembly_forms] [nvarchar](2000) NULL,
	[hact_form_nm] [nvarchar](250) NULL,
	[hact_schema_nm] [nvarchar](250) NULL,
	[hact_schema] [ntext] NULL,
	[hact_url] [nvarchar](2000) NULL,
	[hact_url_template] [nvarchar](2000) NULL,
	[hact_websvc_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_human_activities] PRIMARY KEY CLUSTERED 
(
	[hact_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[domain_configuration_paths]    Script Date: 04/17/2009 14:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[domain_configuration_paths](
	[config_path_id] [int] IDENTITY(1,1) NOT NULL,
	[domain_id] [int] NOT NULL,
	[path] [nvarchar](2000) NULL,
 CONSTRAINT [PK_domain_configuration_paths] PRIMARY KEY CLUSTERED 
(
	[config_path_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[get_activity_data]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-20
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity_data] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- if output data exists: return output data
	IF EXISTS ( SELECT * FROM humanactivity_outputdata WHERE activity_id = @activity_id )
	BEGIN
		SELECT * FROM humanactivity_outputdata WHERE activity_id = @activity_id
	END 
	-- else if form data exist: return form data
	ELSE IF EXISTS ( SELECT * FROM humanactivity_formdata WHERE activity_id = @activity_id )
	BEGIN
		SELECT * FROM humanactivity_formdata WHERE activity_id = @activity_id
	END
	-- else: return input data
	ELSE
	BEGIN
		SELECT * FROM humanactivity_inputdata WHERE activity_id = @activity_id
	END

END
GO
/****** Object:  StoredProcedure [dbo].[get_activity_inputdata]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity_inputdata] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM humanactivity_inputdata WHERE activity_id = @activity_id
END
GO
/****** Object:  StoredProcedure [dbo].[get_activity_formdata]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity_formdata] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT *
	FROM humanactivity_formdata
	WHERE activity_id = @activity_id

END
GO
/****** Object:  StoredProcedure [dbo].[get_activity_outputdata]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity_outputdata] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM humanactivity_outputdata WHERE activity_id = @activity_id

END
GO
/****** Object:  Table [dbo].[key_performance_indicators]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[key_performance_indicators](
	[kpi_id] [int] IDENTITY(1,1) NOT NULL,
	[kpi_nm] [nvarchar](250) NOT NULL,
	[kpi_type] [nvarchar](250) NULL,
	[kpi_desc] [nvarchar](1000) NULL,
	[kpi_ntext] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[kpi_units] [nvarchar](1000) NULL,
	[kpi_datatype] [nvarchar](100) NULL,
	[kpi_calculationtype] [nvarchar](1) NULL,
	[kpi_nullvalue] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_key_performance_indicators] PRIMARY KEY CLUSTERED 
(
	[kpi_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[add_workflow_instance]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2009-01-19
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_workflow_instance] 
	-- Add the parameters for the stored procedure here
	@workflow_instance_id uniqueidentifier, 
	@process_nm nvarchar(250)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF NOT EXISTS ( SELECT * FROM workflow_instances WHERE workflow_instance_id = @workflow_instance_id )
	BEGIN
		INSERT INTO workflow_instances ( workflow_instance_id, process_nm, created_dt )
		VALUES ( @workflow_instance_id, @process_nm, GETDATE() )
	END
    
END
GO
/****** Object:  Table [dbo].[activity_inbox]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activity_inbox](
	[activity_id] [int] IDENTITY(1,1) NOT NULL,
	[workflow_instance_id] [uniqueidentifier] NULL,
	[activity_nm] [nvarchar](250) NULL,
	[process_nm] [nvarchar](250) NULL,
	[subject] [nvarchar](250) NULL,
	[hact_id] [int] NOT NULL,
	[status_id] [int] NULL,
	[reply_uri] [nvarchar](2000) NULL,
	[message_id] [nvarchar](100) NULL,
	[activity_user_id] [int] NULL,
	[opened_dt] [datetime] NULL,
	[completed_dt] [datetime] NULL,
	[created_dt] [datetime] NULL,
	[started_dt] [datetime] NULL,
	[activation_dt] [datetime] NULL,
	[due_dt] [datetime] NULL,
	[postpone_dt] [datetime] NULL,
	[faulted_dt] [datetime] NULL,
	[aborted_dt] [datetime] NULL,
	[stopped_dt] [datetime] NULL,
 CONSTRAINT [PK_activity_inbox] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[brule_ruleconstants]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[brule_ruleconstants](
	[brule_id] [int] NOT NULL,
	[ruleconst_id] [int] NOT NULL,
 CONSTRAINT [PK_brule_ruleconstants] PRIMARY KEY CLUSTERED 
(
	[brule_id] ASC,
	[ruleconst_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[brule_nouns]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[brule_nouns](
	[brule_id] [int] NOT NULL,
	[noun_id] [int] NOT NULL,
 CONSTRAINT [PK_brule_nouns] PRIMARY KEY CLUSTERED 
(
	[brule_id] ASC,
	[noun_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[bobj_items]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bobj_items](
	[bobj_id] [int] NOT NULL,
	[item_bobj_id] [int] NULL,
	[item_noun_id] [int] NULL,
	[item_nm] [nvarchar](250) NULL,
	[item_minoccurs] [int] NULL,
	[item_maxoccurs] [int] NULL,
	[item_is_attribute] [bit] NOT NULL,
	[item_properties] [ntext] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[get_repository_parents_of_type]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2006-10-26
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_repository_parents_of_type] 
	-- Add the parameters for the stored procedure here
	@reptype_id int, 
	@item_id int,
	@parent_reptype_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- declare variables for cursor
	DECLARE @current_reptype_id int, @current_id int
	DECLARE @info nvarchar(2000)

	-- create temporary tables
	CREATE TABLE #result_parents ( parent_reptype_id int, parent_id int, parent_display nvarchar(250) )
	CREATE TABLE #current_parents ( parent_reptype_id int, parent_id int, parent_display nvarchar(250) )
	CREATE TABLE #old_current_parents ( parent_reptype_id int, parent_id int, parent_display nvarchar(250) )
	CREATE TABLE #done ( parent_reptype_id int, parent_id int )

	-- insert the parents of the initial repository item into the #current_parents table
	INSERT INTO #current_parents
	SELECT parent_reptype_id, parent_id, parent_display
	FROM repository_hierarchy
	WHERE child_reptype_id = @reptype_id AND child_id = @item_id AND parent_reptype_id IS NOT NULL

	-- loop while the #current_parents table contains items
	WHILE (SELECT Count(*) FROM #current_parents) > 0
	BEGIN
		-- insert that values from #current_parents into the #result_parents
		-- where the parent_reptype_id equals the requested value
		INSERT INTO #result_parents
		SELECT * FROM #current_parents WHERE parent_reptype_id = @parent_reptype_id
		
		-- clear the #old_current_parents table
		DELETE FROM #old_current_parents
		
		-- insert that values from #current_parents into the #old_current_parents
		-- where the parent_reptype_id not equals the requested value
		INSERT INTO #old_current_parents
		SELECT * FROM #current_parents WHERE parent_reptype_id != @parent_reptype_id

		-- clear the #current_parents table
		DELETE FROM #current_parents

		-- create a cursor for the items in the #old_current_parents table
		DECLARE newchild_cur CURSOR
		FOR SELECT parent_reptype_id, parent_id FROM #old_current_parents

		OPEN newchild_cur

		FETCH NEXT FROM newchild_cur 
		INTO @current_reptype_id, @current_id

		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF NOT EXISTS (
				SELECT * FROM #done
				WHERE parent_reptype_id = @current_reptype_id AND parent_id = @current_id )
			BEGIN
--				PRINT 'type: ' + convert(nvarchar,@current_reptype_id) + '; id: ' + convert(nvarchar,@current_id)

				-- insert the parents of the current item into the #current_parents table
				INSERT INTO #current_parents
				SELECT parent_reptype_id, parent_id, parent_display
				FROM repository_hierarchy
				WHERE child_reptype_id = @current_reptype_id AND child_id = @current_id AND
					  parent_reptype_id IS NOT NULL

				INSERT INTO #done
				VALUES (@current_reptype_id, @current_id)
			END

--			select * from #result_parents
--			select * from #old_current_parents
--			select * from #current_parents
--			select * from #done

			FETCH NEXT FROM newchild_cur 
			INTO @current_reptype_id, @current_id
		END

		CLOSE newchild_cur
		DEALLOCATE newchild_cur

	END

	-- return all values from the #result_parents table
	SELECT DISTINCT * FROM #result_parents

	-- delete temporary tables
	DROP TABLE #result_parents
	DROP TABLE #current_parents
	DROP TABLE #old_current_parents
END
GO
/****** Object:  StoredProcedure [dbo].[get_all_humanactivities]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-22
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_all_humanactivities] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT * FROM human_activities WHERE is_deleted = 0
    
END
GO
/****** Object:  Table [dbo].[kpi_metrics]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kpi_metrics](
	[metric_id] [int] IDENTITY(1,1) NOT NULL,
	[metric_nm] [nvarchar](250) NOT NULL,
	[metric_desc] [nvarchar](1000) NULL,
	[metric_ntext] [ntext] NULL,
	[metric_datatype] [nvarchar](100) NULL,
	[metric_value] [nvarchar](1000) NULL,
	[metric_unit] [nvarchar](50) NULL,
	[org_id] [int] NOT NULL,
	[kpi_id] [int] NULL,
 CONSTRAINT [PK_kpi_metrics] PRIMARY KEY CLUSTERED 
(
	[metric_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[logical_webmethods]    Script Date: 04/17/2009 14:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logical_webmethods](
	[log_wmeth_id] [int] IDENTITY(1,1) NOT NULL,
	[log_wmeth_nm] [nvarchar](250) NOT NULL,
	[log_wmeth_desc] [nvarchar](1000) NULL,
	[log_wmeth_ntext] [ntext] NULL,
	[lm_group_id] [int] NULL,
	[websvc_id] [int] NULL,
	[webmethod_id] [int] NULL,
	[callback_webmethod_id] [int] NULL,
	[proxy_webmethod_id] [int] NULL,
	[proxy_callback_wmeth_id] [int] NULL,
	[hact_id] [int] NULL,
	[input_bobj_id] [int] NULL,
	[input_xslt] [ntext] NULL,
	[input_connections] [ntext] NULL,
	[output_bobj_id] [int] NULL,
	[output_xslt] [ntext] NULL,
	[output_connections] [ntext] NULL,
	[proxy_input_xslt] [ntext] NULL,
	[proxy_input_connections] [ntext] NULL,
	[proxy_output_xslt] [ntext] NULL,
	[proxy_output_connections] [ntext] NULL,
	[endpoint_brule_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_logical_webmethods] PRIMARY KEY CLUSTERED 
(
	[log_wmeth_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[users]    Script Date: 04/17/2009 14:02:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
	[user_id] [int] IDENTITY(1,1) NOT NULL,
	[org_id] [int] NOT NULL,
	[user_nm] [nvarchar](30) NOT NULL,
	[password] [nvarchar](88) NULL,
	[last_nm] [nvarchar](40) NOT NULL,
	[first_nm] [nvarchar](30) NOT NULL,
	[middle_nm] [nvarchar](30) NULL,
	[work_phone] [nvarchar](20) NULL,
	[fax_no] [nvarchar](20) NULL,
	[email_activity_ind] [bit] NULL,
	[email_addr] [nvarchar](60) NULL,
	[active_ind] [bit] NULL,
	[lang_id] [int] NOT NULL,
	[sa_ind] [bit] NULL,
	[cmpl_activity_days] [int] NOT NULL,
	[screen_res_id] [int] NOT NULL,
	[deleted_ind] [bit] NOT NULL,
	[addr] [nvarchar](60) NULL,
	[city] [nvarchar](20) NULL,
	[state] [nvarchar](20) NULL,
	[zip] [nvarchar](10) NULL,
	[country] [nvarchar](20) NULL,
	[title] [nvarchar](60) NULL,
	[mgr1_user_id] [int] NULL,
	[mgr1_assist_user_id] [int] NULL,
	[mgr2_user_id] [int] NULL,
	[mgr2_assist_user_id] [int] NULL,
	[company] [nvarchar](60) NULL,
	[department] [nvarchar](60) NULL,
	[office] [nvarchar](60) NULL,
	[assistant_phone] [nvarchar](20) NULL,
	[business_phone_2] [nvarchar](20) NULL,
	[home_phone] [nvarchar](20) NULL,
	[home_phone_2] [nvarchar](20) NULL,
	[mobile_phone] [nvarchar](20) NULL,
	[pager] [nvarchar](20) NULL,
	[notes] [nvarchar](2000) NULL,
	[lcid] [int] NULL,
	[tz_id] [int] NULL,
	[timezone_id] [nvarchar](40) NULL,
	[daylight_savings_ind] [bit] NOT NULL,
	[personnel_id] [nvarchar](60) NULL,
	[person_nm] [nvarchar](250) NULL,
	[person_desc] [nvarchar](1000) NULL,
	[person_ntext] [ntext] NULL,
	[person_roles] [ntext] NULL,
	[person_orgunit] [int] NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
 CONSTRAINT [XPKuser] PRIMARY KEY CLUSTERED 
(
	[user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [XAK1users] UNIQUE NONCLUSTERED 
(
	[user_nm] ASC,
	[org_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[role]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[role](
	[role_id] [int] IDENTITY(1,1) NOT NULL,
	[org_id] [int] NOT NULL,
	[role_nm] [nvarchar](60) NOT NULL,
	[role_desc] [nvarchar](1000) NULL,
	[role_ntext] [ntext] NULL,
	[role_isunitspecific] [bit] NULL,
	[role_org_id] [int] NULL,
	[role_orgunit_id] [int] NULL,
	[deleted_by] [int] NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
 CONSTRAINT [XPKrole] PRIMARY KEY CLUSTERED 
(
	[role_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[organization_unit]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[organization_unit](
	[org_unit_id] [int] IDENTITY(1,1) NOT NULL,
	[org_id] [int] NOT NULL,
	[org_unit_nm] [nvarchar](60) NOT NULL,
	[pred_org_unit_id] [int] NULL,
	[org_unit_path] [nvarchar](700) NULL,
	[no_admin_outside] [tinyint] NULL,
	[org_unit_desc] [nvarchar](1000) NULL,
	[org_unit_ntext] [ntext] NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
 CONSTRAINT [XPKorganization_unit] PRIMARY KEY CLUSTERED 
(
	[org_unit_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[services]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[services](
	[service_id] [int] IDENTITY(1,1) NOT NULL,
	[service_nm] [nvarchar](250) NOT NULL,
	[service_desc] [nvarchar](1000) NULL,
	[service_type] [int] NULL,
	[service_ntext] [ntext] NULL,
	[service_ws_connections] [ntext] NULL,
	[service_properties] [ntext] NULL,
	[service_hact_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_services] PRIMARY KEY CLUSTERED 
(
	[service_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[workflows]    Script Date: 04/17/2009 14:02:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[workflows](
	[workflow_id] [int] IDENTITY(1,1) NOT NULL,
	[workflow_nm] [nvarchar](250) NOT NULL,
	[workflow_desc] [nvarchar](1000) NULL,
	[workflow_ntext] [ntext] NULL,
	[workflow_namespace] [nvarchar](4000) NULL,
	[workflow_path] [nvarchar](2000) NULL,
	[workflow_logactcode] [ntext] NULL,
	[workflow_srvcode] [ntext] NULL,
	[workflow_codebeside] [ntext] NULL,
	[workflow_wfhostcode] [ntext] NULL,
	[workflow_rules] [ntext] NULL,
	[workflow_coderules] [ntext] NULL,
	[workflow_mappingcode] [ntext] NULL,
	[workflow_datacode] [ntext] NULL,
	[workflow_contractscode] [ntext] NULL,
	[workflow_interfacecode] [ntext] NULL,
	[workflow_localsvccode] [ntext] NULL,
	[workflow_ruleids] [nvarchar](2000) NULL,
	[workflow_properties] [ntext] NULL,
	[workflow_type] [int] NULL,
	[workflow_input_bobj_id] [int] NULL,
	[workflow_output_bobj_id] [int] NULL,
	[workflow_start_nm] [nvarchar](250) NULL,
	[workflow_start_desc] [nvarchar](1000) NULL,
	[workflow_buildtype] [int] NULL,
	[workflow_websvc_id] [int] NULL,
	[domain_config_path_id] [int] NULL,
	[diagram] [ntext] NULL,
	[bus_diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_workflows] PRIMARY KEY CLUSTERED 
(
	[workflow_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[postpone_activity]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-11-10
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[postpone_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int, 
	@user_id int,
	@postpone_dt datetime,
	@new_status_id int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- update activity
	UPDATE activity_inbox
	SET postpone_dt = @postpone_dt
	WHERE activity_id = @activity_id

		-- select new status output
	SELECT @new_status_id = status_id FROM activity_inbox WHERE activity_id = @activity_id
	
END
GO
/****** Object:  Table [dbo].[workflow_brules]    Script Date: 04/17/2009 14:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[workflow_brules](
	[workflow_id] [int] NOT NULL,
	[brule_id] [int] NOT NULL,
 CONSTRAINT [PK_workflow_brules] PRIMARY KEY CLUSTERED 
(
	[workflow_id] ASC,
	[brule_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[wf_methods]    Script Date: 04/17/2009 14:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wf_methods](
	[wf_method_id] [int] IDENTITY(1,1) NOT NULL,
	[wf_method_nm] [nvarchar](250) NOT NULL,
	[wf_method_desc] [nvarchar](1000) NULL,
	[wf_method_ntext] [ntext] NULL,
	[wf_method_input_bobj_id] [int] NULL,
	[wf_method_output_bobj_id] [int] NULL,
	[workflow_id] [int] NULL,
	[org_id] [int] NULL,
	[wf_method_properties] [ntext] NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_wf_methods] PRIMARY KEY CLUSTERED 
(
	[wf_method_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[wf_fields]    Script Date: 04/17/2009 14:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wf_fields](
	[wf_field_id] [int] IDENTITY(1,1) NOT NULL,
	[wf_field_nm] [nvarchar](250) NOT NULL,
	[wf_field_desc] [nvarchar](1000) NULL,
	[wf_field_ntext] [ntext] NULL,
	[workflow_id] [int] NULL,
	[org_id] [int] NULL,
	[wf_field_bobj_id] [int] NULL,
	[wf_field_datatype] [nvarchar](100) NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_wf_fields] PRIMARY KEY CLUSTERED 
(
	[wf_field_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[wf_events]    Script Date: 04/17/2009 14:02:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wf_events](
	[wf_event_id] [int] IDENTITY(1,1) NOT NULL,
	[wf_event_nm] [nvarchar](250) NOT NULL,
	[wf_event_desc] [nvarchar](1000) NULL,
	[wf_event_ntext] [ntext] NULL,
	[wf_event_bobj_id] [int] NULL,
	[wf_event_is_start] [bit] NULL,
	[workflow_id] [int] NULL,
	[wf_event_properties] [ntext] NULL,
	[org_id] [int] NULL,
	[diagram] [ntext] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_wf_events] PRIMARY KEY CLUSTERED 
(
	[wf_event_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[verbs]    Script Date: 04/17/2009 14:02:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[verbs](
	[verb_id] [int] IDENTITY(1,1) NOT NULL,
	[verb_nm] [nvarchar](250) NOT NULL,
	[verb_desc] [nvarchar](1000) NULL,
	[verb_ntext] [ntext] NULL,
	[verb_input_bobj_id] [int] NULL,
	[verb_output_bobj_id] [int] NULL,
	[capability_id] [int] NULL,
	[log_wmeth_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_domain_verbs] PRIMARY KEY CLUSTERED 
(
	[verb_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[service_webservice]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[service_webservice](
	[service_id] [int] NOT NULL,
	[websvc_id] [int] NOT NULL,
 CONSTRAINT [PK_service_webservice] PRIMARY KEY CLUSTERED 
(
	[service_id] ASC,
	[websvc_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[service_modules]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[service_modules](
	[svcmodule_id] [int] IDENTITY(1,1) NOT NULL,
	[svcmodule_nm] [nvarchar](250) NOT NULL,
	[svcmodule_desc] [nvarchar](1000) NULL,
	[svcmodule_ntext] [ntext] NULL,
	[svcmodule_type] [int] NULL,
	[svcmodule_properties] [ntext] NULL,
	[workflow_id] [int] NULL,
	[capability_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_service_modules] PRIMARY KEY CLUSTERED 
(
	[svcmodule_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[sobas]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sobas](
	[soba_id] [int] IDENTITY(1,1) NOT NULL,
	[soba_nm] [nvarchar](250) NOT NULL,
	[soba_desc] [nvarchar](1000) NULL,
	[soba_ntext] [ntext] NULL,
	[workflow_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_sobas] PRIMARY KEY CLUSTERED 
(
	[soba_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[update_activity]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-11-12
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[update_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int, 
	@user_id int,
	@activity_nm nvarchar(250),
	@process_nm nvarchar(250),
	@subject nvarchar(250),
	@reply_uri nvarchar(2000),
	@message_id nvarchar(100)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE activity_inbox
	SET	activity_nm = @activity_nm,
		process_nm = @process_nm,
		subject = @subject,
		reply_uri = @reply_uri,
		message_id = @message_id
	WHERE activity_id = @activity_id
    
END
GO
/****** Object:  Table [dbo].[user_role_assoc]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[user_role_assoc](
	[role_id] [int] NOT NULL,
	[user_id] [int] NOT NULL,
 CONSTRAINT [PK_user_role_assoc] PRIMARY KEY CLUSTERED 
(
	[role_id] ASC,
	[user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[user_participants]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[user_participants](
	[activity_id] [int] NOT NULL,
	[user_id] [int] NOT NULL,
	[participation_type_id] [int] NOT NULL,
 CONSTRAINT [PK_user_participants] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC,
	[user_id] ASC,
	[participation_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[user_login_log]    Script Date: 04/17/2009 14:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[user_login_log](
	[login_id] [uniqueidentifier] NOT NULL,
	[user_id] [int] NOT NULL,
	[login_time_stamp] [datetime] NOT NULL,
	[logout_time_stamp] [datetime] NULL,
	[application_id] [uniqueidentifier] NULL,
 CONSTRAINT [PK_user_login_log] PRIMARY KEY CLUSTERED 
(
	[login_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[org_unit_participants]    Script Date: 04/17/2009 14:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[org_unit_participants](
	[activity_id] [int] NOT NULL,
	[org_unit_id] [int] NOT NULL,
	[participation_type_id] [int] NOT NULL,
 CONSTRAINT [PK_org_unit_participants] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC,
	[org_unit_id] ASC,
	[participation_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[org_participants]    Script Date: 04/17/2009 14:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[org_participants](
	[activity_id] [int] NOT NULL,
	[org_id] [int] NOT NULL,
	[participation_type_id] [int] NOT NULL,
 CONSTRAINT [PK_org_participants] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC,
	[org_id] ASC,
	[participation_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[logmeth_rule_webmethods]    Script Date: 04/17/2009 14:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logmeth_rule_webmethods](
	[log_wmeth_id] [int] NOT NULL,
	[endpoint_brule_result] [nvarchar](250) NOT NULL,
	[webmethod_id] [int] NULL,
	[callback_webmethod_id] [int] NULL,
	[proxy_webmethod_id] [int] NULL,
	[proxy_callback_wmeth_id] [int] NULL,
	[hact_id] [int] NULL,
	[input_xslt] [ntext] NULL,
	[input_connections] [ntext] NULL,
	[output_xslt] [ntext] NULL,
	[output_connections] [ntext] NULL,
	[proxy_input_xslt] [ntext] NULL,
	[proxy_input_connections] [ntext] NULL,
	[proxy_output_xslt] [ntext] NULL,
	[proxy_output_connections] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[notes]    Script Date: 04/17/2009 14:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[notes](
	[note_id] [int] IDENTITY(1,1) NOT NULL,
	[workflow_instance_id] [uniqueidentifier] NOT NULL,
	[activity_id] [int] NULL,
	[note_subject] [nvarchar](250) NOT NULL,
	[note_txt] [ntext] NULL,
	[created_by] [int] NOT NULL,
	[created_dt] [datetime] NULL,
	[lastmodified_by] [int] NULL,
	[lastmodified_dt] [datetime] NULL,
 CONSTRAINT [PK_notes] PRIMARY KEY CLUSTERED 
(
	[note_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[role_participants]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[role_participants](
	[activity_id] [int] NOT NULL,
	[role_id] [int] NOT NULL,
	[participation_type_id] [int] NOT NULL,
 CONSTRAINT [PK_role_participants] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC,
	[role_id] ASC,
	[participation_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[organization_unit_user_assoc]    Script Date: 04/17/2009 14:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[organization_unit_user_assoc](
	[org_unit_id] [int] NOT NULL,
	[user_id] [int] NOT NULL,
 CONSTRAINT [XPKorganization_unit_user_assoc] PRIMARY KEY CLUSTERED 
(
	[org_unit_id] ASC,
	[user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[kpi_functions]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kpi_functions](
	[function_id] [int] IDENTITY(1,1) NOT NULL,
	[function_nm] [nvarchar](250) NOT NULL,
	[function_desc] [nvarchar](1000) NULL,
	[function_ntext] [ntext] NULL,
	[function_code] [ntext] NULL,
	[metric_id] [int] NOT NULL,
 CONSTRAINT [PK_kpi_functions] PRIMARY KEY CLUSTERED 
(
	[function_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[get_activity_form]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-22
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity_form] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT * FROM human_activities ha
    JOIN activity_inbox ai ON ai.hact_id = ha.hact_id
    WHERE ai.activity_id = @activity_id
    
END
GO
/****** Object:  StoredProcedure [dbo].[get_activity]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		get_activity
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM activity_inbox WHERE activity_id = @activity_id

END
GO
/****** Object:  Table [dbo].[attachment]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[attachment](
	[attachment_id] [int] IDENTITY(1,1) NOT NULL,
	[workflow_instance_id] [uniqueidentifier] NOT NULL,
	[activity_id] [int] NULL,
	[attachment_nm] [nvarchar](250) NULL,
	[attachment_desc] [nvarchar](1000) NOT NULL,
	[attachment_filenm] [nvarchar](2000) NULL,
	[attachment_data] [image] NULL,
	[created_by] [int] NULL,
	[created_dt] [datetime] NULL,
	[lastmodified_by] [int] NULL,
	[lastmodified_dt] [datetime] NULL,
 CONSTRAINT [PK_attachment_1] PRIMARY KEY CLUSTERED 
(
	[attachment_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[activity_user_participants]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activity_user_participants](
	[activity_id] [int] NOT NULL,
	[user_of_activity_id] [int] NOT NULL,
	[participation_type_id] [int] NOT NULL,
 CONSTRAINT [PK_activity_user_participants] PRIMARY KEY CLUSTERED 
(
	[activity_id] ASC,
	[user_of_activity_id] ASC,
	[participation_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[activity_log]    Script Date: 04/17/2009 14:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activity_log](
	[activity_id] [int] NOT NULL,
	[activity_log_type_id] [int] NOT NULL,
	[activity_log_dt] [datetime] NOT NULL,
	[activity_log_user_id] [int] NULL,
	[activity_log_text] [nvarchar](1000) NOT NULL,
	[activity_log_details] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[add_user_participant]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_user_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO user_participants ( activity_id, [user_id], participation_type_id )
	VALUES ( @activity_id, @user_id, @participation_type_id )
	
END
GO
/****** Object:  StoredProcedure [dbo].[add_role_participant]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_role_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@role_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO role_participants ( activity_id, role_id, participation_type_id )
	VALUES ( @activity_id, @role_id, @participation_type_id )
END
GO
/****** Object:  StoredProcedure [dbo].[add_orgunit_participant]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_orgunit_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@org_unit_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO org_unit_participants ( activity_id, org_unit_id, participation_type_id )
	VALUES ( @activity_id, @org_unit_id, @participation_type_id )
	
END
GO
/****** Object:  StoredProcedure [dbo].[add_org_participant]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 1008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_org_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int, 
	@org_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO org_participants ( activity_id, org_id, participation_type_id )
	VALUES ( @activity_id, @org_id, @participation_type_id )

END
GO
/****** Object:  StoredProcedure [dbo].[add_note]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_note] 
	-- Add the parameters for the stored procedure here
	@workflow_instance_id uniqueidentifier,
	@activity_id int,
	@note_subject nvarchar(250),
	@note_txt ntext,
	@created_by_user_id int,
	@note_id int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO notes
		( workflow_instance_id, activity_id, note_subject, note_txt, created_by, created_dt )
	VALUES
		( @workflow_instance_id, @activity_id, @note_subject, @note_txt, @created_by_user_id, GETDATE() )
		
	SET @note_id = @@IDENTITY
END
GO
/****** Object:  StoredProcedure [dbo].[add_attachment]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	adds a new attachment
-- =============================================
CREATE PROCEDURE [dbo].[add_attachment] 
	-- Add the parameters for the stored procedure here
	@workflow_instance_id uniqueidentifier,
	@activity_id int,
	@attachment_nm nvarchar(250),
	@attachment_desc nvarchar(1000),
	@attachment_filenm nvarchar(2000),
	@attachment_data image,
	@created_by_user_id int,
	@attachment_id int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO attachment
		( workflow_instance_id, activity_id, attachment_nm, attachment_desc, attachment_filenm, attachment_data,
		  created_by, created_dt )
	VALUES
		( @workflow_instance_id, @activity_id, @attachment_nm, @attachment_desc, @attachment_filenm, @attachment_data,
		  @created_by_user_id, GETDATE() )
		  
	SET @attachment_id = @@IDENTITY
    
END
GO
/****** Object:  StoredProcedure [dbo].[add_activityuser_participant]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_activityuser_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_of_activity_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO activity_user_participants
		( activity_id, user_of_activity_id, participation_type_id )
	VALUES
		( @activity_id, @user_of_activity_id, @participation_type_id )
		
END
GO
/****** Object:  StoredProcedure [dbo].[add_activity_log]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[add_activity_log] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@activity_log_type_id int,
	@activity_log_text nvarchar(1000),
	@activity_log_details ntext = NULL,
	@user_id int = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO activity_log
		( activity_id, activity_log_type_id, activity_log_dt, activity_log_user_id, [activity_log_text], activity_log_details )
	VALUES
		( @activity_id, @activity_log_type_id, GETDATE(), @user_id, @activity_log_text, @activity_log_details)
		
END
GO
/****** Object:  StoredProcedure [dbo].[get_user_participants]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-21
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_user_participants] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM user_participants WHERE activity_id = @activity_id
	
END
GO
/****** Object:  StoredProcedure [dbo].[get_role_participants]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-21
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_role_participants] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM role_participants WHERE activity_id = @activity_id

END
GO
/****** Object:  StoredProcedure [dbo].[delete_note]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[delete_note] 
	-- Add the parameters for the stored procedure here
	@note_id int,
	@user_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM notes WHERE note_id = @note_id

END
GO
/****** Object:  StoredProcedure [dbo].[delete_attachment]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[delete_attachment] 
	-- Add the parameters for the stored procedure here
	@attachment_id int,
	@user_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DELETE FROM attachment WHERE attachment_id = @attachment_id
	
END
GO
/****** Object:  StoredProcedure [dbo].[get_activityuser_participants]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-21
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activityuser_participants] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM activity_user_participants WHERE activity_id = @activity_id
    
END
GO
/****** Object:  StoredProcedure [dbo].[get_activity_log]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity_log] 
	-- Add the parameters for the stored procedure here
	@activity_id int = NULL, 
	@user_id int = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @activity_id IS NOT NULL AND @user_id IS NOT NULL
	BEGIN
		SELECT * FROM activity_log WHERE activity_id = @activity_id AND activity_log_user_id = @user_id
	END
	ELSE IF @activity_id IS NOT NULL AND @user_id IS NULL
	BEGIN
		SELECT * FROM activity_log WHERE activity_id = @activity_id
	END
	ELSE IF @activity_id IS NULL AND @user_id IS NOT NULL
	BEGIN
		SELECT * FROM activity_log WHERE activity_log_user_id = @user_id
	END
	ELSE
	BEGIN
		SELECT * FROM activity_log
	END
	
END
GO
/****** Object:  StoredProcedure [dbo].[get_activity_inbox]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_activity_inbox] 
	-- Add the parameters for the stored procedure here
	@user_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @userroles table(role_id int)
	DECLARE @userorgunits table(org_unit_id int)
	DECLARE @useracts table(activity_id int)
	DECLARE @userorgid int
	
	-- get roles of user
	INSERT INTO @userroles ( role_id )
	SELECT role_id
	FROM user_role_assoc
	WHERE [user_id] = @user_id
	-- get org units of user
	INSERT INTO @userorgunits ( org_unit_id )
	SELECT org_unit_id
	FROM organization_unit_user_assoc
	WHERE [user_id] = @user_id
	-- get activities of user
	INSERT INTO @useracts ( activity_id )
	SELECT activity_id
	FROM activity_inbox
	WHERE activity_user_id = @user_id
	-- get org of user
	SELECT @userorgid = org_id
	FROM users
	WHERE [user_id] = @user_id

	-- select inbox for the current user
	SELECT DISTINCT
		ai.*, ha.hact_url,
		( SELECT COUNT(a.attachment_id) FROM attachment a WHERE a.activity_id = ai.activity_id) as attachment_count
	FROM activity_inbox ai
		LEFT JOIN org_participants op ON op.activity_id = ai.activity_id
		LEFT JOIN org_unit_participants oup ON oup.activity_id = ai.activity_id
		LEFT JOIN role_participants rp ON rp.activity_id = ai.activity_id
		LEFT JOIN user_participants up ON up.activity_id = ai.activity_id
		LEFT JOIN activity_user_participants aup ON aup.activity_id = ai.activity_id
		INNER JOIN human_activities ha ON ha.hact_id = ai.hact_id
	WHERE
		(
			op.org_id = @userorgid OR
			oup.org_unit_id IN ( SELECT uou.org_unit_id FROM @userorgunits uou ) OR
			rp.role_id IN ( SELECT ur.role_id FROM @userroles ur ) OR
			up.[user_id] = @user_id OR
			aup.activity_id IN ( SELECT ua.activity_id FROM @useracts ua ) 
		) AND (
			ai.status_id = 0 OR ai.status_id = 1
		)
END
GO
/****** Object:  StoredProcedure [dbo].[get_orgunit_participants]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-21
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_orgunit_participants] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM org_unit_participants WHERE activity_id = @activity_id

END
GO
/****** Object:  StoredProcedure [dbo].[get_org_participants]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-21
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_org_participants] 
	-- Add the parameters for the stored procedure here
	@activity_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM org_participants WHERE activity_id = @activity_id

END
GO
/****** Object:  StoredProcedure [dbo].[get_notes]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_notes] 
	-- Add the parameters for the stored procedure here
	@workflow_instance_id uniqueidentifier = NULL,
	@activity_id int = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @workflow_instance_id IS NOT NULL AND @activity_id IS NOT NULL
	BEGIN
		SELECT *
		FROM notes
		WHERE activity_id = @activity_id AND workflow_instance_id = @workflow_instance_id
	END
	ELSE IF @workflow_instance_id IS NOT NULL
	BEGIN
		SELECT *
		FROM notes
		WHERE workflow_instance_id = @workflow_instance_id
	END
	ELSE IF @activity_id IS NOT NULL
	BEGIN
		SELECT *
		FROM notes n
		JOIN activity_inbox ai ON ai.workflow_instance_id = n.workflow_instance_id
		WHERE ai.activity_id = @activity_id
	END
	ELSE
	BEGIN
		SELECT *
		FROM notes
	END
    
END
GO
/****** Object:  StoredProcedure [dbo].[get_note]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_note] 
	-- Add the parameters for the stored procedure here
	@note_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM notes WHERE note_id = @note_id
	
END
GO
/****** Object:  StoredProcedure [dbo].[get_attachments_list]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_attachments_list] 
	-- Add the parameters for the stored procedure here
	@workflow_instance_id uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT attachment_id, attachment_nm, attachment_desc, attachment_filenm, created_by, created_dt
	FROM attachment
	WHERE workflow_instance_id = @workflow_instance_id
END
GO
/****** Object:  StoredProcedure [dbo].[get_attachment_data]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[get_attachment_data] 
	-- Add the parameters for the stored procedure here
	@attachment_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT attachment_data FROM attachment WHERE attachment_id = @attachment_id
END
GO
/****** Object:  Table [dbo].[kpi_schedule]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kpi_schedule](
	[schedule_id] [int] IDENTITY(1,1) NOT NULL,
	[schedule_nm] [nvarchar](250) NOT NULL,
	[schedule_desc] [nvarchar](1000) NULL,
	[schedule_ntext] [ntext] NULL,
	[schedule_timespan] [nvarchar](100) NULL,
	[function_id] [int] NOT NULL,
	[schedule_startdt] [datetime] NULL,
	[schedule_enddt] [datetime] NULL,
 CONSTRAINT [PK_kpi_schedule] PRIMARY KEY CLUSTERED 
(
	[schedule_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[logical_activities]    Script Date: 04/17/2009 14:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logical_activities](
	[log_act_id] [int] IDENTITY(1,1) NOT NULL,
	[log_act_nm] [nvarchar](250) NOT NULL,
	[log_act_desc] [nvarchar](1000) NULL,
	[log_act_ntext] [ntext] NULL,
	[log_act_type] [int] NULL,
	[log_act_verb_id] [int] NULL,
	[log_act_svcmodule_id] [int] NULL,
	[log_act_operation_id] [int] NULL,
	[log_act_loginput_connections] [ntext] NULL,
	[log_act_loginput_xslt] [ntext] NULL,
	[log_act_physinput_connections] [ntext] NULL,
	[log_act_physinput_xslt] [ntext] NULL,
	[log_act_input_bobj_id] [int] NULL,
	[log_act_output_bobj_id] [int] NULL,
	[log_act_brule_id] [int] NULL,
	[log_act_properties] [ntext] NULL,
	[log_act_wf_method_id] [int] NULL,
	[log_act_wf_event_id] [int] NULL,
	[log_act_websvc_id] [int] NULL,
	[log_act_webmethod_id] [int] NULL,
	[log_act_input_connections] [ntext] NULL,
	[log_act_input_xslt] [ntext] NULL,
	[log_act_output_connections] [ntext] NULL,
	[log_act_output_xslt] [ntext] NULL,
	[log_act_hact_id] [int] NULL,
	[workflow_id] [int] NULL,
	[log_wmeth_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NOT NULL,
	[domain_id] [int] NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_logical_activities] PRIMARY KEY CLUSTERED 
(
	[log_act_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The type of the logical activity.
Values:
0 - LogicalOperationActivity (=> LogicalEPActivity)
1 - LogicalCapabilityActivity
2 - LogicalAsyncStartActivity
3 - LogicalAsyncEndActivity
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'logical_activities', @level2type=N'COLUMN',@level2name=N'log_act_type'
GO
/****** Object:  StoredProcedure [dbo].[remove_user_participant]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[remove_user_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM user_participants
	WHERE activity_id = @activity_id AND
		  user_id = @user_id AND
		  participation_type_id = @participation_type_id
		  
END
GO
/****** Object:  StoredProcedure [dbo].[remove_role_participant]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[remove_role_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@role_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM role_participants
	WHERE activity_id = @activity_id AND
		  role_id = @role_id AND
		  participation_type_id = @participation_type_id

END
GO
/****** Object:  StoredProcedure [dbo].[remove_orgunit_participant]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[remove_orgunit_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@org_unit_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM org_unit_participants
	WHERE activity_id = @activity_id AND
		  org_unit_id = @org_unit_id AND
		  participation_type_id = @participation_type_id

END
GO
/****** Object:  StoredProcedure [dbo].[remove_org_participant]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[remove_org_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@org_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM org_participants
	WHERE activity_id = @activity_id AND
		  org_id = @org_id AND
		  participation_type_id = @participation_type_id

END
GO
/****** Object:  StoredProcedure [dbo].[remove_activityuser_participant]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[remove_activityuser_participant] 
	-- Add the parameters for the stored procedure here
	@activity_id int, 
	@user_of_activity_id int,
	@participation_type_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM activity_user_participants
	WHERE activity_id = @activity_id AND user_of_activity_id = @user_of_activity_id AND
		  participation_type_id = @participation_type_id
    
END
GO
/****** Object:  StoredProcedure [dbo].[logout_user]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************************************/
CREATE PROCEDURE [dbo].[logout_user]
	@login_id uniqueidentifier,
	@application_id uniqueidentifier
	
--WITH ENCRYPTION
AS
SET NOCOUNT ON

DECLARE @user_id int
--

UPDATE user_login_log
SET logout_time_stamp = GETDATE()
WHERE login_id = @login_id AND application_id = @application_id AND logout_time_stamp IS NULL

--
RETURN
GO
/****** Object:  StoredProcedure [dbo].[login_user]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************************************/
CREATE PROCEDURE [dbo].[login_user] 
	@user_nm nvarchar(30), 
	@password nchar(88),
	@org_id int = NULL,
	@lang_id int = NULL,
	@org_nm nvarchar(60) = NULL,
	@application_id uniqueidentifier
	
--WITH ENCRYPTION
AS
SET NOCOUNT ON
DECLARE @in_maint int,
		@return_message nvarchar(2000),
		@install_dt datetime,
		@rtn_status int,
		@web_file_location nvarchar(100),
		@web_addr nvarchar(120),
		@user_id int,
		@translated_label_nm nvarchar(180),
		@user_tz_offset decimal(5,1),
		@real_user_tz_offset decimal(5,1),
		@login_date datetime,
		@user_guid uniqueidentifier,
		@login_id uniqueidentifier
--
SET @login_date = GETDATE()
--   
SELECT	@in_maint = is_in_maintain_mode,
		@web_file_location = web_file_location,
		@web_addr = web_addr
FROM	installation_options

IF @in_maint = 1
BEGIN
	SELECT	'return_code' = 'ERROR',
			'return_message' = 'The system is down for maintenance. Please try back later.',
			'user_nm' = NULL,
			'sa_ind' = 0,
			'screen_res_id' = 3,
			'lang_id' = NULL,
			'org_id' = NULL,
			'can_update_ind' = 0,
			'user_lcid' = NULL,
	        'user_tz_offset' = 0,
			'real_user_tz_offset' = 0,
			'login_id' = NULL,
			'first_nm' = NULL,
			'middle_nm' = NULL,
			'last_nm' = NULL
	RETURN
END

IF @org_id IS NULL AND @org_nm IS NULL
BEGIN	
	SELECT	'return_code' = 'ERROR',
			'return_message' = 'Login requires an organization',
			'user_id' = NULL,
			'sa_ind' = 0,
			'lang_id' = @lang_id,
			'org_id' = NULL,
			'can_update_ind' = 0,
			'user_lcid' = NULL,
			'user_tz_offset' = 0,
			'real_user_tz_offset' = 0,
			'login_id' = NULL,
			'first_nm' = NULL,
			'middle_nm' = NULL,
			'last_nm' = NULL
	RETURN
END
--
SET @user_nm	= LOWER(RTRIM(LTRIM( @user_nm )))
SET @org_nm		= LOWER(RTRIM(LTRIM( @org_nm )))
SET @password	= RTRIM(LTRIM( @password ))
--
IF @org_nm IS NOT NULL
BEGIN 
	IF NOT EXISTS ( SELECT * FROM organization WHERE LOWER( org_nm ) = @org_nm )
	BEGIN		
		SELECT	'return_code' = 'ERROR',
				'return_message' = 'The organization name entered does not exist.',
				'user_id' = NULL,
				'sa_ind' = 0,
				'lang_id' = @lang_id,
				'org_id' = NULL,
				'can_update_ind' = 0,
				'user_lcid' = NULL,
				'user_tz_offset' = 0,
				'real_user_tz_offset' = 0,
				'login_id' = NULL,
				'first_nm' = NULL,
				'middle_nm' = NULL,
				'last_nm' = NULL
		RETURN
	END
	SELECT @org_id = org_id
	FROM organization WHERE LOWER(org_nm) = LOWER(RTRIM( @org_nm ))
END
/*
** if user name and password are correct and
** the user is active, not deleted and a member of the requested organization
*/
IF EXISTS ( SELECT * FROM users WHERE [user_nm] = @user_nm AND [password] = @password AND
									  [active_ind] = 1 AND [deleted_ind] = 0 AND [org_id] = @org_id )
BEGIN
	
	SELECT 	@user_id = [user_id]  
	FROM 	users 
	WHERE 	[user_nm] = @user_nm AND 
			[password] = @password AND 
			[active_ind] = 1 AND 
			[deleted_ind] = 0 AND
			[org_id] = @org_id

	-- if user_login_log contains an entry for this user and application then return an error
	IF EXISTS ( SELECT * FROM user_login_log
				WHERE [user_id] = @user_id AND [application_id] = @application_id AND
					  [logout_time_stamp] IS NULL )
	BEGIN
		SELECT	'return_code' = 'USER_IS_LOGGED_IN',
				'return_message' = 'The user is already logged into the system.',
				'user_id' = @user_id,
				'sa_ind' = 0,
				'lang_id' = @lang_id,
				'org_id' = NULL,
				'can_update_ind' = 0,
				'user_lcid' = NULL,
				'user_tz_offset' = 0,
				'real_user_tz_offset' = 0,
				'login_id' = ( SELECT TOP 1 login_id FROM user_login_log
							   WHERE [user_id] = @user_id AND [application_id] = @application_id AND
							   [logout_time_stamp] IS NULL ORDER BY login_time_stamp DESC ),
				'first_nm' = NULL,
				'middle_nm' = NULL,
				'last_nm' = NULL
		RETURN
		-- EXIT
	END
	--  
	SET @login_id = NEWID()
	
	-- log the user login
	INSERT user_login_log
		( [login_id], [user_id], [login_time_stamp], [application_id] )
	VALUES
		( @login_id, @user_id, @login_date, @application_id )
	--
	SELECT	'return_code' = 'OK',
			'return_message' = @translated_label_nm,
			u.[user_id], 
			u.[sa_ind],
			u.[lang_id],
			u.[org_id],
			o.[can_update_ind],
			'user_lcid' = u.[lcid],
			'user_tz_offset' = 0,
			'real_user_tz_offset' = 0,
			'login_id' = @login_id,
			u.[first_nm],
			u.[middle_nm],
			u.[last_nm]
	FROM 	users u
			JOIN organization o ON o.org_id = u.org_id
	WHERE 	u.[user_id] = @user_id
	
END ELSE BEGIN

	IF EXISTS( SELECT * FROM users WHERE [user_nm] = @user_nm AND [password] <> @password)
	BEGIN
		SELECT	'return_code' = 'ERROR',
				'return_message' = 'You have entered an incorrect password.',
				'user_id' = NULL,
				'sa_ind' = 0,
				'lang_id' = NULL,
				'org_id' = NULL,
				'can_update_ind' = 0,
				'user_lcid' = NULL,
     			'user_tz_offset' = 0,
				'real_user_tz_offset' = 0,
				'login_id' = NULL,
				'first_nm' = NULL,
				'middle_nm' = NULL,
				'last_nm' = NULL
		RETURN
	END ELSE BEGIN
		SELECT	'return_code' = 'ERROR',
				'return_message' = 'You have entered an incorrect username or password.',
				'user_id' = NULL,
				'sa_ind' = 0,
				'lang_id' = NULL,
				'org_id' = NULL,
				'can_update_ind' = 0,
				'user_lcid' = NULL,
      			'user_tz_offset' = 0,
				'real_user_tz_offset' = 0,
				'login_id' = NULL,
				'first_nm' = NULL,
				'middle_nm' = NULL,
				'last_nm' = NULL
		RETURN
	END
END
/**/
RETURN(0)
/**/
GO
/****** Object:  StoredProcedure [dbo].[login_refresh]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2009-02-03
-- Description:	Refreshes the login datetime to verify an active user.
-- =============================================
CREATE PROCEDURE [dbo].[login_refresh] 
	@user_id int, 
	@login_id uniqueidentifier,
	@application_id uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [user_login_log]
	SET [login_time_stamp] = GETDATE()
	WHERE [user_id] = @user_id AND [login_id] = @login_id AND
		  [application_id] = @application_id AND [logout_time_stamp] IS NULL

END
GO
/****** Object:  Table [dbo].[operations]    Script Date: 04/17/2009 14:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[operations](
	[operation_id] [int] IDENTITY(1,1) NOT NULL,
	[operation_nm] [nvarchar](250) NOT NULL,
	[operation_desc] [nvarchar](1000) NULL,
	[operation_ntext] [ntext] NULL,
	[operation_input_schema] [ntext] NULL,
	[operation_input_isbobjxsd] [bit] NOT NULL,
	[operation_input_bobj_id] [int] NULL,
	[operation_input_bobj_nm] [nvarchar](250) NULL,
	[operation_output_schema] [ntext] NULL,
	[operation_output_isbobjxsd] [bit] NOT NULL,
	[operation_output_bobj_id] [int] NULL,
	[operation_output_bobj_nm] [nvarchar](250) NULL,
	[operation_input_formstyle] [ntext] NULL,
	[operation_output_formstyle] [ntext] NULL,
	[operation_workflow_id] [int] NULL,
	[operation_exposed_workflow_id] [int] NULL,
	[operation_verb_id] [int] NULL,
	[exposed_verb_id] [int] NULL,
	[svcmodule_id] [int] NULL,
	[exposed_svcmodule_id] [int] NULL,
	[diagram] [ntext] NULL,
	[org_id] [int] NULL,
	[domain_id] [int] NULL,
	[is_folder] [bit] NOT NULL,
	[is_locked] [bit] NOT NULL,
	[locked_dt] [datetime] NULL,
	[locked_by] [int] NULL,
	[created_type] [int] NULL,
	[created_dt] [datetime] NULL,
	[created_by] [int] NULL,
	[last_modified_dt] [datetime] NULL,
	[last_modified_by] [int] NULL,
	[is_deleted] [bit] NOT NULL,
	[deleted_dt] [datetime] NULL,
	[deleted_by] [int] NULL,
	[version_nr] [int] NULL,
	[version_source_id] [int] NULL,
	[version_last_merge_by] [int] NULL,
	[version_last_merge_dt] [datetime] NULL,
 CONSTRAINT [PK_svcmethods] PRIMARY KEY CLUSTERED 
(
	[operation_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[update_note]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[update_note] 
	-- Add the parameters for the stored procedure here
	@note_id int,
	@note_subject nvarchar(250),
	@note_txt ntext,
	@user_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE notes
	SET	note_subject = @note_subject,
		note_txt = note_txt,
		lastmodified_by = @user_id,
		lastmodified_dt = GETDATE()
	WHERE note_id = @note_id
	
END
GO
/****** Object:  StoredProcedure [dbo].[update_attachment]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[update_attachment] 
	-- Add the parameters for the stored procedure here
	@attachment_id int, 
	@attachment_nm nvarchar(250),
	@attachment_desc nvarchar(1000),
	@attachment_filenm nvarchar(2000),
	@attachment_data image,
	@user_id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @attachment_data IS NULL
	BEGIN
		UPDATE attachment
		SET	attachment_nm = @attachment_nm,
			attachment_desc = @attachment_desc,
			attachment_filenm = @attachment_filenm,
			lastmodified_by = @user_id,
			lastmodified_dt = GETDATE()
		WHERE attachment_id = @attachment_id
	END ELSE BEGIN
		UPDATE attachment
		SET	attachment_nm = @attachment_nm,
			attachment_desc = @attachment_desc,
			attachment_filenm = @attachment_filenm,
			attachment_data = @attachment_data,
			lastmodified_by = @user_id,
			lastmodified_dt = GETDATE()
		WHERE attachment_id = @attachment_id
	END
END
GO
/****** Object:  StoredProcedure [dbo].[stop_activity]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[stop_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int = NULL,
	@stop_message nvarchar(1000) = NULL,
	@stop_details ntext = NULL,
	@new_status_id int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- update activity
	UPDATE activity_inbox
	SET	status_id = 5/*faulted*/,
		stopped_dt = GETDATE()
	WHERE activity_id = @activity_id
	
	-- write log
	EXEC add_activity_log @activity_id, 11/*faulted*/, @stop_message, @stop_details, @user_id
	
	-- select new status output
	SELECT @new_status_id = status_id FROM activity_inbox WHERE activity_id = @activity_id
END
GO
/****** Object:  StoredProcedure [dbo].[start_activity]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-20
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[start_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int, 
	@user_id int,
	@new_status_id int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- set activity status
	UPDATE activity_inbox
	SET	status_id = 1/*in_progress*/,
		activity_user_id = @user_id,
		started_dt = GETDATE()
	WHERE activity_id = @activity_id
	
	-- write log
	EXEC add_activity_log @activity_id, 3/*started*/, 'Activity started.', NULL, @user_id

	-- select new status output
	SELECT @new_status_id = status_id FROM activity_inbox WHERE activity_id = @activity_id
END
GO
/****** Object:  StoredProcedure [dbo].[search_repository_items]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2007-09-05
-- Description:	Searching for repository items.
-- =============================================
-- Change Log:
--	2008-01-17 by rene_e
--		- add queries for new tables capabilities and logical_method_groups
--	2008-05-22 by Josef Lindner
--		- added parameter @case_sensitve and added check for case sensitivity to queries
--			(will not work correctly if sql server is set to case insensitiv)
--		- added column domain_nm to all queries
--	2009-01-13 by rene_e
--		- required changes for cleanup
--		- changed user_key to user_id
-- =============================================
CREATE PROCEDURE [dbo].[search_repository_items] 
	-- Add the parameters for the stored procedure here
	@org_id int,
	@domain_id int,
	@name nvarchar(250),
	@desc nvarchar(1000) = NULL,
	@type int = NULL,
	--@ntext nvarchar(250) = NULL,
	@search_exact bit = 1,
	@case_sensitive bit = 0

--WITH ENCRYPTION
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @current_type int

	CREATE TABLE #tmp (
		id int,
		[type] int,
		[name] nvarchar(250),
		[desc] nvarchar(1000),
		org_id int,
		domain_id int,
		domain_nm nvarchar(250),
		is_locked bit,
		locked_dt datetime,
		locked_by int,
		locked_nm nvarchar(250),
		created_type int,
		created_dt datetime,
		created_by int,
		created_nm nvarchar(250),
		last_modified_dt datetime,
		last_modified_by int,
		last_modified_nm nvarchar(250)
	)

	IF @search_exact = 0
	BEGIN
		SET @name = '%' + @name + '%'
		SET @desc = '%' + @desc + '%'
		--SET @ntext = '%' + @ntext + '%'
	END

	-- generic_folder
	SET @current_type = 16

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.folder_id AS id, @current_type as [type], a.folder_nm AS [name], a.folder_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM generic_folder a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.folder_nm LIKE @name) OR (lower(a.folder_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.folder_desc LIKE @desc) OR (lower(a.folder_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.folder_nm
	END
	-- domains
	SET @current_type = 1

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.domain_id AS id, @current_type AS [type], a.domain_nm AS [name], a.domain_desc AS [desc],
			a.org_id, a.parent_domain_id AS domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM domains a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.domain_nm LIKE @name) OR (lower(a.domain_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.domain_desc LIKE @desc) OR (lower(a.domain_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.parent_domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.domain_nm
	END

	-- capabilities
	SET @current_type = 84

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.capability_id AS id, @current_type AS [type], a.capability_nm AS [name], a.capability_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM capabilities a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.capability_nm LIKE @name) OR (lower(a.capability_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.capability_desc LIKE @desc) OR (lower(a.capability_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.capability_nm
	END

	-- verbs
	SET @current_type = 14

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.verb_id AS id, @current_type AS [type], a.verb_nm AS [name], a.verb_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM verbs a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.verb_nm LIKE @name) OR (lower(a.verb_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.verb_desc LIKE @desc) OR (lower(a.verb_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.verb_nm
	END

	-- nouns
	SET @current_type = 4

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.noun_id AS id, @current_type AS [type], a.noun_nm AS [name], a.noun_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM nouns a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.noun_nm LIKE @name) OR (lower(a.noun_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.noun_desc LIKE @desc) OR (lower(a.noun_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.noun_nm
	END

	-- business_objects
	SET @current_type = 12

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.bobj_id AS id, @current_type AS [type], a.bobj_nm AS [name], a.bobj_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM business_objects a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.bobj_nm LIKE @name) OR (lower(a.bobj_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.bobj_desc LIKE @desc) OR (lower(a.bobj_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.bobj_nm
	END

	-- business_policies
	SET @current_type = 17

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.bpolicy_id AS id, @current_type AS [type], a.bpolicy_nm AS [name], a.bpolicy_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM business_policies a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.bpolicy_nm LIKE @name) OR (lower(a.bpolicy_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.bpolicy_desc LIKE @desc) OR (lower(a.bpolicy_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.bpolicy_nm
	END

	-- repository_business_rules
	SET @current_type = 10

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.brule_id AS id, @current_type AS [type], a.brule_nm AS [name], a.brule_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM repository_business_rules a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.brule_nm LIKE @name) OR (lower(a.brule_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.brule_desc LIKE @desc) OR (lower(a.brule_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.brule_nm
	END

	-- service_modules
	SET @current_type = 36

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.svcmodule_id AS id, @current_type AS [type], a.svcmodule_nm AS [name], a.svcmodule_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM service_modules a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.svcmodule_nm LIKE @name) OR (lower(a.svcmodule_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.svcmodule_desc LIKE @desc) OR (lower(a.svcmodule_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.svcmodule_nm
	END

	-- services
	SET @current_type = 6

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.service_id AS id, @current_type AS [type], a.service_nm AS [name], a.service_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM services a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.service_nm LIKE @name) OR (lower(a.service_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.service_desc LIKE @desc) OR (lower(a.service_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.service_nm
	END

	-- operations
	SET @current_type = 8

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.operation_id AS id, @current_type AS [type], a.operation_nm AS [name], a.operation_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM operations a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.operation_nm LIKE @name) OR (lower(a.operation_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.operation_desc LIKE @desc) OR (lower(a.operation_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.operation_nm
	END

	-- repository_diagrams
	SET @current_type = 37

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.diagram_id AS id, @current_type AS [type], a.diagram_nm AS [name], a.diagram_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM repository_diagrams a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.diagram_nm LIKE @name) OR (lower(a.diagram_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.diagram_desc LIKE @desc) OR (lower(a.diagram_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.diagram_nm
	END

	-- webservices
	SET @current_type = 59

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.websvc_id AS id, @current_type AS [type], a.websvc_nm AS [name], a.websvc_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM webservices a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.websvc_nm LIKE @name) OR (lower(a.websvc_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.websvc_desc LIKE @desc) OR (lower(a.websvc_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.websvc_nm
	END
	-- webmethods
	SET @current_type = 60

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.webmethod_id AS id, @current_type AS [type], a.webmethod_nm AS [name], a.webmethod_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM webmethods a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.webmethod_nm LIKE @name) OR (lower(a.webmethod_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.webmethod_desc LIKE @desc) OR (lower(a.webmethod_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.webmethod_nm
	END

	-- workflows
	SET @current_type = 58

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.workflow_id AS id, @current_type AS [type], a.workflow_nm AS [name], a.workflow_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM workflows a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.workflow_nm LIKE @name) OR (lower(a.workflow_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.workflow_desc LIKE @desc) OR (lower(a.workflow_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.workflow_nm
	END

	-- systems
	--SET @current_type = 47

	--IF ( @type IS NULL OR @type = @current_type )
	--BEGIN
	--	INSERT INTO #tmp
	--	SELECT a.system_id AS id, @current_type AS [type], a.system_nm AS [name], a.system_desc AS [desc],
	--		a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
	--		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
	--		a.created_type, a.created_dt, a.created_by,
	--		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
	--		a.last_modified_dt, a.last_modified_by,
	--		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
	--	FROM systems a INNER JOIN domains d ON a.domain_id = d.domain_id
	--	WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.system_nm LIKE @name) OR (lower(a.system_nm) LIKE lower(@name)) ) ) AND
	--		  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.system_desc LIKE @desc) OR (lower(a.system_desc) LIKE lower(@desc)) ) ) AND
	--		  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
	--		  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
	--	ORDER BY a.system_nm
	--END

	-- sobas
	SET @current_type = 54

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.soba_id AS id, @current_type AS [type], a.soba_nm AS [name], a.soba_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM sobas a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.soba_nm LIKE @name) OR (lower(a.soba_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.soba_desc LIKE @desc) OR (lower(a.soba_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.soba_nm
	END

	-- rule constants
	SET @current_type = 74

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.ruleconst_id AS id, @current_type AS [type], a.ruleconst_nm AS [name], a.ruleconst_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM rule_constants a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.ruleconst_nm LIKE @name) OR (lower(a.ruleconst_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.ruleconst_desc LIKE @desc) OR (lower(a.ruleconst_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.ruleconst_nm
	END

	-- human activities
	SET @current_type = 76

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.hact_id AS id, @current_type AS [type], a.hact_nm AS [name], a.hact_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM human_activities a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.hact_nm LIKE @name) OR (lower(a.hact_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.hact_desc LIKE @desc) OR (lower(a.hact_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.hact_nm
	END
	-- logical activities
	SET @current_type = 67

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.log_act_id AS id, @current_type AS [type], a.log_act_nm AS [name], a.log_act_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM logical_activities a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.log_act_nm LIKE @name) OR (lower(a.log_act_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.log_act_desc LIKE @desc) OR (lower(a.log_act_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.log_act_nm
	END

	-- logical method groups
	SET @current_type = 85

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.lm_group_id AS id, @current_type AS [type], a.lm_group_nm AS [name], a.lm_group_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM logical_method_groups a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.lm_group_nm LIKE @name) OR (lower(a.lm_group_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.lm_group_desc LIKE @desc) OR (lower(a.lm_group_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.lm_group_nm
	END

	-- logical web methods
	SET @current_type = 81

	IF ( @type IS NULL OR @type = @current_type )
	BEGIN
		INSERT INTO #tmp
		SELECT a.log_wmeth_id AS id, @current_type AS [type], a.log_wmeth_nm AS [name], a.log_wmeth_desc AS [desc],
			a.org_id, a.domain_id, d.domain_nm, a.is_locked, a.locked_dt, a.locked_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
			a.created_type, a.created_dt, a.created_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
			a.last_modified_dt, a.last_modified_by,
			(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by)
		FROM logical_webmethods a INNER JOIN domains d ON a.domain_id = d.domain_id
		WHERE ( @name IS NULL OR ((@case_sensitive = 1 AND a.log_wmeth_nm LIKE @name) OR (lower(a.log_wmeth_nm) LIKE lower(@name)) ) ) AND
			  ( @desc IS NULL OR ((@case_sensitive = 1 AND a.log_wmeth_desc LIKE @desc) OR (lower(a.log_wmeth_desc) LIKE lower(@desc)) ) ) AND
			  ( @domain_id IS NULL OR a.domain_id = @domain_id ) AND
			  ( a.org_id = @org_id ) AND ( a.is_deleted = 0 ) AND ( d.is_deleted = 0)
		ORDER BY a.log_wmeth_nm
	END

	-- return #tmp
	SELECT * FROM #tmp

	-- delete #tmp
	DROP TABLE #tmp
END
GO
/****** Object:  Table [dbo].[operation_webmethod]    Script Date: 04/17/2009 14:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[operation_webmethod](
	[operation_id] [int] NOT NULL,
	[webmethod_id] [int] NOT NULL,
	[input_connections] [ntext] NULL,
	[input_xslt] [ntext] NULL,
	[output_connections] [ntext] NULL,
	[output_xslt] [ntext] NULL,
 CONSTRAINT [PK_operation_webmethod] PRIMARY KEY CLUSTERED 
(
	[operation_id] ASC,
	[webmethod_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[operation_log_ep]    Script Date: 04/17/2009 14:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[operation_log_ep](
	[operation_id] [int] NOT NULL,
	[log_ep_guid] [uniqueidentifier] NOT NULL,
	[input_xslt] [ntext] NULL,
	[input_mapping] [ntext] NULL,
 CONSTRAINT [PK_svcmethod_activity] PRIMARY KEY CLUSTERED 
(
	[operation_id] ASC,
	[log_ep_guid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'logical endpoint guid of a process activity
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'operation_log_ep', @level2type=N'COLUMN',@level2name=N'log_ep_guid'
GO
/****** Object:  Table [dbo].[logicalactivity_bindings]    Script Date: 04/17/2009 14:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logicalactivity_bindings](
	[log_act_id] [int] NOT NULL,
	[websvc_id] [int] NOT NULL,
	[webmethod_id] [int] NOT NULL,
	[hact_id] [int] NULL,
	[input_connections] [ntext] NULL,
	[input_xslt] [ntext] NULL,
	[output_connections] [ntext] NULL,
	[output_xslt] [ntext] NULL,
	[properties] [ntext] NULL,
 CONSTRAINT [PK_logicalactivity_bindings] PRIMARY KEY CLUSTERED 
(
	[log_act_id] ASC,
	[websvc_id] ASC,
	[webmethod_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[log_act_bobjects]    Script Date: 04/17/2009 14:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[log_act_bobjects](
	[log_act_id] [int] NOT NULL,
	[bobj_id] [int] NOT NULL,
	[is_subscription] [bit] NOT NULL,
	[published_log_act_id] [int] NOT NULL,
 CONSTRAINT [PK_log_act_bobjects] PRIMARY KEY CLUSTERED 
(
	[log_act_id] ASC,
	[bobj_id] ASC,
	[is_subscription] ASC,
	[published_log_act_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[get_repository_child_items]    Script Date: 04/17/2009 14:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2006-10-23
-- Description:	Returns one table with all child elements of an repository item with detail informations.
-- =============================================
-- Change Log:
--	2008-01-17 by rene_e
--		- add queries for tables capabilities and logical_method_groups
--  2009-02-02 by rene_e
--		- add query for message queues
-- =============================================
CREATE PROCEDURE [dbo].[get_repository_child_items] 
	-- Add the parameters for the stored procedure here
	@type_id int, 
	@item_id int,
	@org_id int = 0,
	@child_type_id int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @current_type int

	CREATE TABLE #tmp (
		id int,
		[type] int,
		[name] nvarchar(250),
		[desc] nvarchar(1000),
		org_id int,
		is_locked bit,
		locked_dt datetime,
		locked_by int,
		locked_nm nvarchar(250),
		created_type int,
		created_dt datetime,
		created_by int,
		created_nm nvarchar(250),
		last_modified_dt datetime,
		last_modified_by int,
		last_modified_nm nvarchar(250),
		custom nvarchar(100)
	)

	-- generic_folder
	SET @current_type = 16

	INSERT INTO #tmp
	SELECT a.folder_id AS id, @current_type as [type], a.folder_nm AS [name], a.folder_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM generic_folder a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.folder_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.folder_nm

	-- domains
	SET @current_type = 1

	INSERT INTO #tmp
	SELECT a.domain_id AS id, @current_type AS [type], a.domain_nm AS [name], a.domain_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM domains a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.domain_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.domain_nm

	-- capabilities
	SET @current_type = 84

	INSERT INTO #tmp
	SELECT a.capability_id AS id, @current_type AS [type], a.capability_nm AS [name], a.capability_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM capabilities a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.capability_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.capability_nm

	-- verbs
	SET @current_type = 14

	INSERT INTO #tmp
	SELECT a.verb_id AS id, @current_type AS [type], a.verb_nm AS [name], a.verb_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM verbs a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.verb_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.verb_nm

	-- nouns
	SET @current_type = 4

	INSERT INTO #tmp
	SELECT a.noun_id AS id, @current_type AS [type], a.noun_nm AS [name], a.noun_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		a.noun_datatype as custom
	FROM nouns a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.noun_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.noun_nm

	-- business_objects
	SET @current_type = 12

	INSERT INTO #tmp
	SELECT a.bobj_id AS id, @current_type AS [type], a.bobj_nm AS [name], a.bobj_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM business_objects a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.bobj_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.bobj_nm

	-- business_policies
	SET @current_type = 17

	INSERT INTO #tmp
	SELECT a.bpolicy_id AS id, @current_type AS [type], a.bpolicy_nm AS [name], a.bpolicy_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM business_policies a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.bpolicy_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.bpolicy_nm

	-- repository_business_rules
	SET @current_type = 10

	INSERT INTO #tmp
	SELECT a.brule_id AS id, @current_type AS [type], a.brule_nm AS [name], a.brule_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM repository_business_rules a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.brule_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.brule_nm

	-- service_modules
	SET @current_type = 36

	INSERT INTO #tmp
	SELECT a.svcmodule_id AS id, @current_type AS [type], a.svcmodule_nm AS [name], a.svcmodule_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM service_modules a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.svcmodule_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.svcmodule_nm

	-- services
	SET @current_type = 6

	INSERT INTO #tmp
	SELECT a.service_id AS id, @current_type AS [type], a.service_nm AS [name], a.service_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM services a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.service_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.service_nm

	-- operations
	SET @current_type = 8

	INSERT INTO #tmp
	SELECT a.operation_id AS id, @current_type AS [type], a.operation_nm AS [name], a.operation_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM operations a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.operation_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.operation_nm

	-- repository_diagrams
	SET @current_type = 37

	INSERT INTO #tmp
	SELECT a.diagram_id AS id, @current_type AS [type], a.diagram_nm AS [name], a.diagram_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM repository_diagrams a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.diagram_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.diagram_nm

	-- webservices
	SET @current_type = 59

	INSERT INTO #tmp
	SELECT a.websvc_id AS id, @current_type AS [type], a.websvc_nm AS [name], a.websvc_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM webservices a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.websvc_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.websvc_nm

	-- webmethods
	SET @current_type = 60

	INSERT INTO #tmp
	SELECT a.webmethod_id AS id, @current_type AS [type], a.webmethod_nm AS [name], a.webmethod_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM webmethods a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.webmethod_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.webmethod_nm

	-- workflows
	SET @current_type = 58

	INSERT INTO #tmp
	SELECT a.workflow_id AS id, @current_type AS [type], a.workflow_nm AS [name], a.workflow_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM workflows a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.workflow_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.workflow_nm

	-- systems
	--SET @current_type = 47

	--INSERT INTO #tmp
	--SELECT a.system_id AS id, @current_type AS [type], a.system_nm AS [name], a.system_desc AS [desc],
	--	a.org_id, a.is_locked, a.locked_dt, a.locked_by,
	--	(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
	--	a.created_type, a.created_dt, a.created_by,
	--	(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
	--	a.last_modified_dt, a.last_modified_by,
	--	(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
	--	NULL as custom
	--FROM systems a
	--INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.system_id
	--WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
	--	  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	--ORDER BY a.system_nm

	-- sobas
	SET @current_type = 54

	INSERT INTO #tmp
	SELECT a.soba_id AS id, @current_type AS [type], a.soba_nm AS [name], a.soba_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM sobas a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.soba_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.soba_nm

	-- rule constants
	SET @current_type = 74

	INSERT INTO #tmp
	SELECT a.ruleconst_id AS id, @current_type AS [type], a.ruleconst_nm AS [name], a.ruleconst_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		a.ruleconst_value as custom
	FROM rule_constants a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.ruleconst_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.ruleconst_nm

	-- human activities
	SET @current_type = 76

	INSERT INTO #tmp
	SELECT a.hact_id AS id, @current_type AS [type], a.hact_nm AS [name], a.hact_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM human_activities a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.hact_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.hact_nm

	-- logical method groups
	SET @current_type = 85

	INSERT INTO #tmp
	SELECT a.lm_group_id AS id, @current_type AS [type], a.lm_group_nm AS [name], a.lm_group_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM logical_method_groups a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.lm_group_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.lm_group_nm

	-- logical webmethods
	SET @current_type = 81

	INSERT INTO #tmp
	SELECT a.log_wmeth_id AS id, @current_type AS [type], a.log_wmeth_nm AS [name], a.log_wmeth_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM logical_webmethods a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.log_wmeth_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.log_wmeth_nm

	-- message queue
	SET @current_type = 88

	INSERT INTO #tmp
	SELECT a.websvc_id AS id, @current_type AS [type], a.websvc_nm AS [name], a.websvc_desc AS [desc],
		a.org_id, a.is_locked, a.locked_dt, a.locked_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.locked_by),
		a.created_type, a.created_dt, a.created_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.created_by),
		a.last_modified_dt, a.last_modified_by,
		(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.[user_id] = a.last_modified_by),
		NULL as custom
	FROM webservices a
	INNER JOIN repository_hierarchy rh ON rh.child_reptype_id = @current_type AND rh.child_id = a.websvc_id
	WHERE (rh.parent_reptype_id = @type_id AND rh.parent_id = @item_id) OR @type_id = 0 AND
		  (a.org_id = @org_id OR @org_id <= 0) AND (@child_type_id <= 0 OR @child_type_id = @current_type)
	ORDER BY a.websvc_nm

	-- return #tmp
	SELECT * FROM #tmp

	-- delete #tmp
	DROP TABLE #tmp
END
GO
/****** Object:  StoredProcedure [dbo].[complete_activity]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-15
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[complete_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int, 
	@user_id int,
	@new_status_id int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- update activity
	UPDATE activity_inbox
	SET status_id = 2, /*completed*/
		completed_dt = GETDATE()
	WHERE activity_id = @activity_id
	
	-- write log
	EXEC add_activity_log @activity_id, 8 /*completed*/, 'Activity completed.', NULL, @user_id

	-- select new status output
	SELECT @new_status_id = status_id FROM activity_inbox WHERE activity_id = @activity_id
END
GO
/****** Object:  StoredProcedure [dbo].[fault_activity]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-20
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[fault_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int = NULL,
	@fault_message nvarchar(1000) = NULL,
	@fault_details ntext = NULL,
	@new_status_id int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- update activity
	UPDATE activity_inbox
	SET	status_id = 4/*faulted*/,
		faulted_dt = GETDATE()
	WHERE activity_id = @activity_id
	
	-- write log
	EXEC add_activity_log @activity_id, 900/*faulted*/, @fault_message, @fault_details, @user_id
	
	-- select new status output
	SELECT @new_status_id = status_id FROM activity_inbox WHERE activity_id = @activity_id
END
GO
/****** Object:  StoredProcedure [dbo].[create_activity]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-14
-- Description:	creates a new human activity inbox item
-- =============================================
CREATE PROCEDURE [dbo].[create_activity] 
	-- Add the parameters for the stored procedure here
	@workflow_instance_id uniqueidentifier, 
	@activity_nm nvarchar(250),
	@process_nm nvarchar(250),
	@subject nvarchar(250),
	@hact_id int,
	@reply_uri nvarchar(2000),
	@message_id nvarchar(100),
	@due_dt datetime,
	@activity_id int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO activity_inbox
		( workflow_instance_id, activity_nm, process_nm, subject, hact_id, status_id, reply_uri, message_id,
		  created_dt )
	VALUES
		( @workflow_instance_id, @activity_nm, @process_nm, @subject, @hact_id, 0/*not_started*/, @reply_uri, @message_id,
		  GETDATE() )
		  
	SET @activity_id = @@IDENTITY
		  
	EXEC add_activity_log @activity_id, 2/*initialized*/,'Activity created.', NULL, NULL
	
END
GO
/****** Object:  StoredProcedure [dbo].[cancel_activity]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-20
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[cancel_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int,
	@new_status_id int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- delete form data
	DELETE FROM humanactivity_formdata WHERE activity_id = @activity_id
	
	-- set activity status to not_started
	UPDATE activity_inbox
	SET	status_id = 0/*not started*/,
		started_dt = NULL
	WHERE activity_id = @activity_id
	
	-- write log
	EXEC add_activity_log @activity_id, 7/*canceled*/, 'Activity canceled.', NULL, @user_id
	
	-- select new status output
	SELECT @new_status_id = status_id FROM activity_inbox WHERE activity_id = @activity_id
END
GO
/****** Object:  StoredProcedure [dbo].[abort_activity]    Script Date: 04/17/2009 14:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		rene_e
-- Create date: 2008-10-20
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[abort_activity] 
	-- Add the parameters for the stored procedure here
	@activity_id int,
	@user_id int,
	@abort_message nvarchar(1000),
	@abort_details ntext = NULL,
	@new_status_id int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- set activity status
	UPDATE activity_inbox
	SET	status_id = 3/*aborted*/,
		aborted_dt = GETDATE()
	WHERE activity_id = @activity_id
	
	-- write log
	EXEC add_activity_log @activity_id, 901/*aborted*/, @abort_message, @abort_details, @user_id
	
	-- select new status output
	SELECT @new_status_id = status_id FROM activity_inbox WHERE activity_id = @activity_id
END
GO
/****** Object:  Default [DF_bobj_items_item_is_attribute]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[bobj_items] ADD  CONSTRAINT [DF_bobj_items_item_is_attribute]  DEFAULT ((0)) FOR [item_is_attribute]
GO
/****** Object:  Default [DF_business_objects_is_folder]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[business_objects] ADD  CONSTRAINT [DF_business_objects_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_business_objects_is_locked]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[business_objects] ADD  CONSTRAINT [DF_business_objects_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_business_objects_is_deleted]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[business_objects] ADD  CONSTRAINT [DF_business_objects_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_business_policies_is_folder]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[business_policies] ADD  CONSTRAINT [DF_business_policies_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_business_policies_is_locked]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[business_policies] ADD  CONSTRAINT [DF_business_policies_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_business_policies_is_deleted]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[business_policies] ADD  CONSTRAINT [DF_business_policies_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_capabilites_is_locked]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[capabilities] ADD  CONSTRAINT [DF_capabilites_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_capabilites_is_deleted]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[capabilities] ADD  CONSTRAINT [DF_capabilites_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_configurations_config_guid]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[configurations] ADD  CONSTRAINT [DF_configurations_config_guid]  DEFAULT (newid()) FOR [config_guid]
GO
/****** Object:  Default [DF_document_schemas_doc_schema_guid]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[document_schemas] ADD  CONSTRAINT [DF_document_schemas_doc_schema_guid]  DEFAULT (newid()) FOR [doc_schema_guid]
GO
/****** Object:  Default [DF_document_schemas_is_simple_type]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[document_schemas] ADD  CONSTRAINT [DF_document_schemas_is_simple_type]  DEFAULT ((0)) FOR [is_simple_type]
GO
/****** Object:  Default [DF_documentations_is_locked]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[documentations] ADD  CONSTRAINT [DF_documentations_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_documentations_is_deleted]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[documentations] ADD  CONSTRAINT [DF_documentations_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_domains_is_folder]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[domains] ADD  CONSTRAINT [DF_domains_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_domains_is_locked]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[domains] ADD  CONSTRAINT [DF_domains_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_domains_is_deleted]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[domains] ADD  CONSTRAINT [DF_domains_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_generic_folder_is_user_folder]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[generic_folder] ADD  CONSTRAINT [DF_generic_folder_is_user_folder]  DEFAULT ((0)) FOR [is_user_folder]
GO
/****** Object:  Default [DF_generic_folder_is_locked]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[generic_folder] ADD  CONSTRAINT [DF_generic_folder_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_generic_folder_is_deleted]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[generic_folder] ADD  CONSTRAINT [DF_generic_folder_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_human_activities_is_folder]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[human_activities] ADD  CONSTRAINT [DF_human_activities_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_human_activities_is_locked]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[human_activities] ADD  CONSTRAINT [DF_human_activities_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_human_activities_is_deleted]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[human_activities] ADD  CONSTRAINT [DF_human_activities_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_installation_options_day_light_savings_ind]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[installation_options] ADD  CONSTRAINT [DF_installation_options_day_light_savings_ind]  DEFAULT ((0)) FOR [daylight_savings_ind]
GO
/****** Object:  Default [DF_installation_options_is_in_maintain_mode]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[installation_options] ADD  CONSTRAINT [DF_installation_options_is_in_maintain_mode]  DEFAULT ((0)) FOR [is_in_maintain_mode]
GO
/****** Object:  Default [DF_key_performance_indicators_kpi_nullvalue]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[key_performance_indicators] ADD  CONSTRAINT [DF_key_performance_indicators_kpi_nullvalue]  DEFAULT ((0)) FOR [kpi_nullvalue]
GO
/****** Object:  Default [DF_log_act_bobjects_is_subscription]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[log_act_bobjects] ADD  CONSTRAINT [DF_log_act_bobjects_is_subscription]  DEFAULT ((1)) FOR [is_subscription]
GO
/****** Object:  Default [DF_log_act_bobjects_published_log_act_id]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[log_act_bobjects] ADD  CONSTRAINT [DF_log_act_bobjects_published_log_act_id]  DEFAULT ((0)) FOR [published_log_act_id]
GO
/****** Object:  Default [DF_logical_activities_is_locked]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_activities] ADD  CONSTRAINT [DF_logical_activities_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_logical_activities_is_deleted]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_activities] ADD  CONSTRAINT [DF_logical_activities_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_logical_method_groups_is_locked]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_method_groups] ADD  CONSTRAINT [DF_logical_method_groups_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_logical_method_groups_is_deleted]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_method_groups] ADD  CONSTRAINT [DF_logical_method_groups_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_logical_webmethods_is_locked]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods] ADD  CONSTRAINT [DF_logical_webmethods_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_logical_webmethods_is_deleted]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods] ADD  CONSTRAINT [DF_logical_webmethods_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_nouns_is_folder]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[nouns] ADD  CONSTRAINT [DF_nouns_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_nouns_is_locked]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[nouns] ADD  CONSTRAINT [DF_nouns_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_nouns_is_deleted]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[nouns] ADD  CONSTRAINT [DF_nouns_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_svcmethods_svcmethod_input_isbobjxsd]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operations] ADD  CONSTRAINT [DF_svcmethods_svcmethod_input_isbobjxsd]  DEFAULT ((0)) FOR [operation_input_isbobjxsd]
GO
/****** Object:  Default [DF_svcmethods_svcmethod_output_isbobjxsd]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operations] ADD  CONSTRAINT [DF_svcmethods_svcmethod_output_isbobjxsd]  DEFAULT ((0)) FOR [operation_output_isbobjxsd]
GO
/****** Object:  Default [DF_operations_is_folder]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operations] ADD  CONSTRAINT [DF_operations_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_operations_is_locked]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operations] ADD  CONSTRAINT [DF_operations_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_operations_is_deleted]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operations] ADD  CONSTRAINT [DF_operations_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_organization_is_locked]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[organization] ADD  CONSTRAINT [DF_organization_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_organization_is_deleted]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[organization] ADD  CONSTRAINT [DF_organization_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_organization_unit_is_locked]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[organization_unit] ADD  CONSTRAINT [DF_organization_unit_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_organization_unit_is_deleted]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[organization_unit] ADD  CONSTRAINT [DF_organization_unit_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_repositories_is_locked]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repositories] ADD  CONSTRAINT [DF_repositories_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_repositories_is_deleted]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repositories] ADD  CONSTRAINT [DF_repositories_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_repository_business_rules_is_locked]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_business_rules] ADD  CONSTRAINT [DF_repository_business_rules_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_repository_business_rules_is_deleted]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_business_rules] ADD  CONSTRAINT [DF_repository_business_rules_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_repository_diagrams_is_locked]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_diagrams] ADD  CONSTRAINT [DF_repository_diagrams_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_repository_diagrams_is_deleted]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_diagrams] ADD  CONSTRAINT [DF_repository_diagrams_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_repository_hierarchy_position]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_hierarchy] ADD  CONSTRAINT [DF_repository_hierarchy_position]  DEFAULT ((0)) FOR [position]
GO
/****** Object:  Default [DF_role_is_locked]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[role] ADD  CONSTRAINT [DF_role_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_role_is_deleted]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[role] ADD  CONSTRAINT [DF_role_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_rule_constants_is_folder]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[rule_constants] ADD  CONSTRAINT [DF_rule_constants_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_rule_constants_is_locked]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[rule_constants] ADD  CONSTRAINT [DF_rule_constants_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_rule_constants_is_deleted]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[rule_constants] ADD  CONSTRAINT [DF_rule_constants_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_service_modules_is_locked]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[service_modules] ADD  CONSTRAINT [DF_service_modules_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_service_modules_is_deleted]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[service_modules] ADD  CONSTRAINT [DF_service_modules_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_services_is_folder]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[services] ADD  CONSTRAINT [DF_services_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_services_is_locked]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[services] ADD  CONSTRAINT [DF_services_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_services_is_deleted]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[services] ADD  CONSTRAINT [DF_services_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_sobas_is_locked]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[sobas] ADD  CONSTRAINT [DF_sobas_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_sobas_is_deleted]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[sobas] ADD  CONSTRAINT [DF_sobas_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_user_login_log_login_id]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[user_login_log] ADD  CONSTRAINT [DF_user_login_log_login_id]  DEFAULT (newid()) FOR [login_id]
GO
/****** Object:  Default [def_4_bit]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [def_4_bit]  DEFAULT ((0)) FOR [active_ind]
GO
/****** Object:  Default [DF_users_day_light_savings_ind]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_day_light_savings_ind]  DEFAULT ((0)) FOR [daylight_savings_ind]
GO
/****** Object:  Default [DF_users_is_locked]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_users_is_deleted]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_users_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_verbs_is_folder]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[verbs] ADD  CONSTRAINT [DF_verbs_is_folder]  DEFAULT ((0)) FOR [is_folder]
GO
/****** Object:  Default [DF_verbs_is_locked]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[verbs] ADD  CONSTRAINT [DF_verbs_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_verbs_is_deleted]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[verbs] ADD  CONSTRAINT [DF_verbs_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_webmethods_is_locked]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[webmethods] ADD  CONSTRAINT [DF_webmethods_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_webmethods_is_deleted]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[webmethods] ADD  CONSTRAINT [DF_webmethods_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_webservices_is_locked]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[webservices] ADD  CONSTRAINT [DF_webservices_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_webservices_is_deleted]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[webservices] ADD  CONSTRAINT [DF_webservices_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_wf_events_wf_method_is_start]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_events] ADD  CONSTRAINT [DF_wf_events_wf_method_is_start]  DEFAULT ((0)) FOR [wf_event_is_start]
GO
/****** Object:  Default [DF_wf_events_is_locked]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_events] ADD  CONSTRAINT [DF_wf_events_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_wf_events_is_deleted]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_events] ADD  CONSTRAINT [DF_wf_events_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_wf_fields_is_locked]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_fields] ADD  CONSTRAINT [DF_wf_fields_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_wf_fields_is_deleted]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_fields] ADD  CONSTRAINT [DF_wf_fields_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_wf_methods_is_locked]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_methods] ADD  CONSTRAINT [DF_wf_methods_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_wf_methods_is_deleted]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_methods] ADD  CONSTRAINT [DF_wf_methods_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  Default [DF_workflows_is_locked]    Script Date: 04/17/2009 14:02:38 ******/
ALTER TABLE [dbo].[workflows] ADD  CONSTRAINT [DF_workflows_is_locked]  DEFAULT ((0)) FOR [is_locked]
GO
/****** Object:  Default [DF_workflows_is_deleted]    Script Date: 04/17/2009 14:02:38 ******/
ALTER TABLE [dbo].[workflows] ADD  CONSTRAINT [DF_workflows_is_deleted]  DEFAULT ((0)) FOR [is_deleted]
GO
/****** Object:  ForeignKey [FK_activity_inbox_activity_status]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_inbox]  WITH CHECK ADD  CONSTRAINT [FK_activity_inbox_activity_status] FOREIGN KEY([status_id])
REFERENCES [dbo].[activity_status] ([status_id])
GO
ALTER TABLE [dbo].[activity_inbox] CHECK CONSTRAINT [FK_activity_inbox_activity_status]
GO
/****** Object:  ForeignKey [FK_activity_inbox_human_activities]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_inbox]  WITH CHECK ADD  CONSTRAINT [FK_activity_inbox_human_activities] FOREIGN KEY([hact_id])
REFERENCES [dbo].[human_activities] ([hact_id])
GO
ALTER TABLE [dbo].[activity_inbox] CHECK CONSTRAINT [FK_activity_inbox_human_activities]
GO
/****** Object:  ForeignKey [FK_activity_inbox_workflow_instances]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_inbox]  WITH CHECK ADD  CONSTRAINT [FK_activity_inbox_workflow_instances] FOREIGN KEY([workflow_instance_id])
REFERENCES [dbo].[workflow_instances] ([workflow_instance_id])
GO
ALTER TABLE [dbo].[activity_inbox] CHECK CONSTRAINT [FK_activity_inbox_workflow_instances]
GO
/****** Object:  ForeignKey [FK_activity_log_activity_inbox]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_log]  WITH CHECK ADD  CONSTRAINT [FK_activity_log_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[activity_log] CHECK CONSTRAINT [FK_activity_log_activity_inbox]
GO
/****** Object:  ForeignKey [FK_activity_log_activity_log_types]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_log]  WITH CHECK ADD  CONSTRAINT [FK_activity_log_activity_log_types] FOREIGN KEY([activity_log_type_id])
REFERENCES [dbo].[activity_log_types] ([activity_log_type_id])
GO
ALTER TABLE [dbo].[activity_log] CHECK CONSTRAINT [FK_activity_log_activity_log_types]
GO
/****** Object:  ForeignKey [FK_activity_user_participants_activity_inbox]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_user_participants]  WITH CHECK ADD  CONSTRAINT [FK_activity_user_participants_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[activity_user_participants] CHECK CONSTRAINT [FK_activity_user_participants_activity_inbox]
GO
/****** Object:  ForeignKey [FK_activity_user_participants_activity_inbox1]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_user_participants]  WITH CHECK ADD  CONSTRAINT [FK_activity_user_participants_activity_inbox1] FOREIGN KEY([user_of_activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[activity_user_participants] CHECK CONSTRAINT [FK_activity_user_participants_activity_inbox1]
GO
/****** Object:  ForeignKey [FK_activity_user_participants_participation_types]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[activity_user_participants]  WITH CHECK ADD  CONSTRAINT [FK_activity_user_participants_participation_types] FOREIGN KEY([participation_type_id])
REFERENCES [dbo].[participation_types] ([participation_type_id])
GO
ALTER TABLE [dbo].[activity_user_participants] CHECK CONSTRAINT [FK_activity_user_participants_participation_types]
GO
/****** Object:  ForeignKey [FK_attachment_activity_inbox]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[attachment]  WITH CHECK ADD  CONSTRAINT [FK_attachment_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[attachment] CHECK CONSTRAINT [FK_attachment_activity_inbox]
GO
/****** Object:  ForeignKey [FK_attachment_workflow_instances]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[attachment]  WITH CHECK ADD  CONSTRAINT [FK_attachment_workflow_instances] FOREIGN KEY([workflow_instance_id])
REFERENCES [dbo].[workflow_instances] ([workflow_instance_id])
GO
ALTER TABLE [dbo].[attachment] CHECK CONSTRAINT [FK_attachment_workflow_instances]
GO
/****** Object:  ForeignKey [FK_bobj_items_business_objects]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[bobj_items]  WITH NOCHECK ADD  CONSTRAINT [FK_bobj_items_business_objects] FOREIGN KEY([bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[bobj_items] CHECK CONSTRAINT [FK_bobj_items_business_objects]
GO
/****** Object:  ForeignKey [FK_bobj_items_business_objects1]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[bobj_items]  WITH NOCHECK ADD  CONSTRAINT [FK_bobj_items_business_objects1] FOREIGN KEY([item_bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[bobj_items] CHECK CONSTRAINT [FK_bobj_items_business_objects1]
GO
/****** Object:  ForeignKey [FK_bobj_items_nouns]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[bobj_items]  WITH NOCHECK ADD  CONSTRAINT [FK_bobj_items_nouns] FOREIGN KEY([item_noun_id])
REFERENCES [dbo].[nouns] ([noun_id])
GO
ALTER TABLE [dbo].[bobj_items] CHECK CONSTRAINT [FK_bobj_items_nouns]
GO
/****** Object:  ForeignKey [FK_brule_nouns_nouns]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[brule_nouns]  WITH CHECK ADD  CONSTRAINT [FK_brule_nouns_nouns] FOREIGN KEY([noun_id])
REFERENCES [dbo].[nouns] ([noun_id])
GO
ALTER TABLE [dbo].[brule_nouns] CHECK CONSTRAINT [FK_brule_nouns_nouns]
GO
/****** Object:  ForeignKey [FK_brule_nouns_repository_business_rules]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[brule_nouns]  WITH CHECK ADD  CONSTRAINT [FK_brule_nouns_repository_business_rules] FOREIGN KEY([brule_id])
REFERENCES [dbo].[repository_business_rules] ([brule_id])
GO
ALTER TABLE [dbo].[brule_nouns] CHECK CONSTRAINT [FK_brule_nouns_repository_business_rules]
GO
/****** Object:  ForeignKey [FK_brule_ruleconstants_repository_business_rules]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[brule_ruleconstants]  WITH CHECK ADD  CONSTRAINT [FK_brule_ruleconstants_repository_business_rules] FOREIGN KEY([brule_id])
REFERENCES [dbo].[repository_business_rules] ([brule_id])
GO
ALTER TABLE [dbo].[brule_ruleconstants] CHECK CONSTRAINT [FK_brule_ruleconstants_repository_business_rules]
GO
/****** Object:  ForeignKey [FK_brule_ruleconstants_rule_constants]    Script Date: 04/17/2009 14:02:29 ******/
ALTER TABLE [dbo].[brule_ruleconstants]  WITH CHECK ADD  CONSTRAINT [FK_brule_ruleconstants_rule_constants] FOREIGN KEY([ruleconst_id])
REFERENCES [dbo].[rule_constants] ([ruleconst_id])
GO
ALTER TABLE [dbo].[brule_ruleconstants] CHECK CONSTRAINT [FK_brule_ruleconstants_rule_constants]
GO
/****** Object:  ForeignKey [FK_domain_configuration_paths_domains]    Script Date: 04/17/2009 14:02:30 ******/
ALTER TABLE [dbo].[domain_configuration_paths]  WITH CHECK ADD  CONSTRAINT [FK_domain_configuration_paths_domains] FOREIGN KEY([domain_id])
REFERENCES [dbo].[domains] ([domain_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[domain_configuration_paths] CHECK CONSTRAINT [FK_domain_configuration_paths_domains]
GO
/****** Object:  ForeignKey [FK_human_activities_webservices]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[human_activities]  WITH CHECK ADD  CONSTRAINT [FK_human_activities_webservices] FOREIGN KEY([hact_websvc_id])
REFERENCES [dbo].[webservices] ([websvc_id])
GO
ALTER TABLE [dbo].[human_activities] CHECK CONSTRAINT [FK_human_activities_webservices]
GO
/****** Object:  ForeignKey [FK_key_performance_indicators_datatypes]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[key_performance_indicators]  WITH CHECK ADD  CONSTRAINT [FK_key_performance_indicators_datatypes] FOREIGN KEY([kpi_datatype])
REFERENCES [dbo].[datatypes] ([datatype])
GO
ALTER TABLE [dbo].[key_performance_indicators] CHECK CONSTRAINT [FK_key_performance_indicators_datatypes]
GO
/****** Object:  ForeignKey [FK_kpi_functions_kpi_metrics]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[kpi_functions]  WITH CHECK ADD  CONSTRAINT [FK_kpi_functions_kpi_metrics] FOREIGN KEY([metric_id])
REFERENCES [dbo].[kpi_metrics] ([metric_id])
GO
ALTER TABLE [dbo].[kpi_functions] CHECK CONSTRAINT [FK_kpi_functions_kpi_metrics]
GO
/****** Object:  ForeignKey [FK_kpi_metrics_datatypes1]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[kpi_metrics]  WITH CHECK ADD  CONSTRAINT [FK_kpi_metrics_datatypes1] FOREIGN KEY([metric_datatype])
REFERENCES [dbo].[datatypes] ([datatype])
GO
ALTER TABLE [dbo].[kpi_metrics] CHECK CONSTRAINT [FK_kpi_metrics_datatypes1]
GO
/****** Object:  ForeignKey [FK_kpi_metrics_key_performance_indicators]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[kpi_metrics]  WITH CHECK ADD  CONSTRAINT [FK_kpi_metrics_key_performance_indicators] FOREIGN KEY([kpi_id])
REFERENCES [dbo].[key_performance_indicators] ([kpi_id])
GO
ALTER TABLE [dbo].[kpi_metrics] CHECK CONSTRAINT [FK_kpi_metrics_key_performance_indicators]
GO
/****** Object:  ForeignKey [FK_kpi_schedule_kpi_functions]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[kpi_schedule]  WITH CHECK ADD  CONSTRAINT [FK_kpi_schedule_kpi_functions] FOREIGN KEY([function_id])
REFERENCES [dbo].[kpi_functions] ([function_id])
GO
ALTER TABLE [dbo].[kpi_schedule] CHECK CONSTRAINT [FK_kpi_schedule_kpi_functions]
GO
/****** Object:  ForeignKey [FK_log_act_bobjects_business_objects]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[log_act_bobjects]  WITH CHECK ADD  CONSTRAINT [FK_log_act_bobjects_business_objects] FOREIGN KEY([bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[log_act_bobjects] CHECK CONSTRAINT [FK_log_act_bobjects_business_objects]
GO
/****** Object:  ForeignKey [FK_log_act_bobjects_logical_activities]    Script Date: 04/17/2009 14:02:31 ******/
ALTER TABLE [dbo].[log_act_bobjects]  WITH CHECK ADD  CONSTRAINT [FK_log_act_bobjects_logical_activities] FOREIGN KEY([log_act_id])
REFERENCES [dbo].[logical_activities] ([log_act_id])
GO
ALTER TABLE [dbo].[log_act_bobjects] CHECK CONSTRAINT [FK_log_act_bobjects_logical_activities]
GO
/****** Object:  ForeignKey [FK_logical_activities_human_activities]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_activities]  WITH CHECK ADD  CONSTRAINT [FK_logical_activities_human_activities] FOREIGN KEY([log_act_hact_id])
REFERENCES [dbo].[human_activities] ([hact_id])
GO
ALTER TABLE [dbo].[logical_activities] CHECK CONSTRAINT [FK_logical_activities_human_activities]
GO
/****** Object:  ForeignKey [FK_logical_activities_logical_webmethods]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_activities]  WITH CHECK ADD  CONSTRAINT [FK_logical_activities_logical_webmethods] FOREIGN KEY([log_wmeth_id])
REFERENCES [dbo].[logical_webmethods] ([log_wmeth_id])
GO
ALTER TABLE [dbo].[logical_activities] CHECK CONSTRAINT [FK_logical_activities_logical_webmethods]
GO
/****** Object:  ForeignKey [FK_logical_activities_wf_events]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_activities]  WITH CHECK ADD  CONSTRAINT [FK_logical_activities_wf_events] FOREIGN KEY([log_act_wf_event_id])
REFERENCES [dbo].[wf_events] ([wf_event_id])
GO
ALTER TABLE [dbo].[logical_activities] CHECK CONSTRAINT [FK_logical_activities_wf_events]
GO
/****** Object:  ForeignKey [FK_logical_activities_wf_methods]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_activities]  WITH CHECK ADD  CONSTRAINT [FK_logical_activities_wf_methods] FOREIGN KEY([log_act_wf_method_id])
REFERENCES [dbo].[wf_methods] ([wf_method_id])
GO
ALTER TABLE [dbo].[logical_activities] CHECK CONSTRAINT [FK_logical_activities_wf_methods]
GO
/****** Object:  ForeignKey [FK_logical_webmethods_business_objects]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods]  WITH CHECK ADD  CONSTRAINT [FK_logical_webmethods_business_objects] FOREIGN KEY([input_bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[logical_webmethods] CHECK CONSTRAINT [FK_logical_webmethods_business_objects]
GO
/****** Object:  ForeignKey [FK_logical_webmethods_business_objects1]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods]  WITH CHECK ADD  CONSTRAINT [FK_logical_webmethods_business_objects1] FOREIGN KEY([output_bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[logical_webmethods] CHECK CONSTRAINT [FK_logical_webmethods_business_objects1]
GO
/****** Object:  ForeignKey [FK_logical_webmethods_human_activities]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods]  WITH CHECK ADD  CONSTRAINT [FK_logical_webmethods_human_activities] FOREIGN KEY([hact_id])
REFERENCES [dbo].[human_activities] ([hact_id])
GO
ALTER TABLE [dbo].[logical_webmethods] CHECK CONSTRAINT [FK_logical_webmethods_human_activities]
GO
/****** Object:  ForeignKey [FK_logical_webmethods_logical_method_groups]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods]  WITH CHECK ADD  CONSTRAINT [FK_logical_webmethods_logical_method_groups] FOREIGN KEY([lm_group_id])
REFERENCES [dbo].[logical_method_groups] ([lm_group_id])
GO
ALTER TABLE [dbo].[logical_webmethods] CHECK CONSTRAINT [FK_logical_webmethods_logical_method_groups]
GO
/****** Object:  ForeignKey [FK_logical_webmethods_webmethods]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods]  WITH CHECK ADD  CONSTRAINT [FK_logical_webmethods_webmethods] FOREIGN KEY([webmethod_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
GO
ALTER TABLE [dbo].[logical_webmethods] CHECK CONSTRAINT [FK_logical_webmethods_webmethods]
GO
/****** Object:  ForeignKey [FK_logical_webmethods_webmethods1]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods]  WITH CHECK ADD  CONSTRAINT [FK_logical_webmethods_webmethods1] FOREIGN KEY([proxy_webmethod_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[logical_webmethods] CHECK CONSTRAINT [FK_logical_webmethods_webmethods1]
GO
/****** Object:  ForeignKey [FK_logical_webmethods_webservices]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logical_webmethods]  WITH CHECK ADD  CONSTRAINT [FK_logical_webmethods_webservices] FOREIGN KEY([websvc_id])
REFERENCES [dbo].[webservices] ([websvc_id])
GO
ALTER TABLE [dbo].[logical_webmethods] CHECK CONSTRAINT [FK_logical_webmethods_webservices]
GO
/****** Object:  ForeignKey [FK_logicalactivity_bindings_human_activities]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logicalactivity_bindings]  WITH CHECK ADD  CONSTRAINT [FK_logicalactivity_bindings_human_activities] FOREIGN KEY([hact_id])
REFERENCES [dbo].[human_activities] ([hact_id])
GO
ALTER TABLE [dbo].[logicalactivity_bindings] CHECK CONSTRAINT [FK_logicalactivity_bindings_human_activities]
GO
/****** Object:  ForeignKey [FK_logicalactivity_bindings_logical_activities]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logicalactivity_bindings]  WITH CHECK ADD  CONSTRAINT [FK_logicalactivity_bindings_logical_activities] FOREIGN KEY([log_act_id])
REFERENCES [dbo].[logical_activities] ([log_act_id])
GO
ALTER TABLE [dbo].[logicalactivity_bindings] CHECK CONSTRAINT [FK_logicalactivity_bindings_logical_activities]
GO
/****** Object:  ForeignKey [FK_logicalactivity_bindings_webmethods]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logicalactivity_bindings]  WITH CHECK ADD  CONSTRAINT [FK_logicalactivity_bindings_webmethods] FOREIGN KEY([webmethod_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
GO
ALTER TABLE [dbo].[logicalactivity_bindings] CHECK CONSTRAINT [FK_logicalactivity_bindings_webmethods]
GO
/****** Object:  ForeignKey [FK_logicalactivity_bindings_webservices]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logicalactivity_bindings]  WITH CHECK ADD  CONSTRAINT [FK_logicalactivity_bindings_webservices] FOREIGN KEY([websvc_id])
REFERENCES [dbo].[webservices] ([websvc_id])
GO
ALTER TABLE [dbo].[logicalactivity_bindings] CHECK CONSTRAINT [FK_logicalactivity_bindings_webservices]
GO
/****** Object:  ForeignKey [FK_logmeth_rule_webmethods_logical_webmethods]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logmeth_rule_webmethods]  WITH NOCHECK ADD  CONSTRAINT [FK_logmeth_rule_webmethods_logical_webmethods] FOREIGN KEY([log_wmeth_id])
REFERENCES [dbo].[logical_webmethods] ([log_wmeth_id])
GO
ALTER TABLE [dbo].[logmeth_rule_webmethods] CHECK CONSTRAINT [FK_logmeth_rule_webmethods_logical_webmethods]
GO
/****** Object:  ForeignKey [FK_logmeth_rule_webmethods_webmethods]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logmeth_rule_webmethods]  WITH NOCHECK ADD  CONSTRAINT [FK_logmeth_rule_webmethods_webmethods] FOREIGN KEY([webmethod_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
GO
ALTER TABLE [dbo].[logmeth_rule_webmethods] CHECK CONSTRAINT [FK_logmeth_rule_webmethods_webmethods]
GO
/****** Object:  ForeignKey [FK_logmeth_rule_webmethods_webmethods1]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logmeth_rule_webmethods]  WITH NOCHECK ADD  CONSTRAINT [FK_logmeth_rule_webmethods_webmethods1] FOREIGN KEY([proxy_webmethod_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
GO
ALTER TABLE [dbo].[logmeth_rule_webmethods] CHECK CONSTRAINT [FK_logmeth_rule_webmethods_webmethods1]
GO
/****** Object:  ForeignKey [FK_logmeth_rule_webmethods_webmethods2]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logmeth_rule_webmethods]  WITH NOCHECK ADD  CONSTRAINT [FK_logmeth_rule_webmethods_webmethods2] FOREIGN KEY([callback_webmethod_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
GO
ALTER TABLE [dbo].[logmeth_rule_webmethods] CHECK CONSTRAINT [FK_logmeth_rule_webmethods_webmethods2]
GO
/****** Object:  ForeignKey [FK_logmeth_rule_webmethods_webmethods3]    Script Date: 04/17/2009 14:02:32 ******/
ALTER TABLE [dbo].[logmeth_rule_webmethods]  WITH NOCHECK ADD  CONSTRAINT [FK_logmeth_rule_webmethods_webmethods3] FOREIGN KEY([proxy_callback_wmeth_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
GO
ALTER TABLE [dbo].[logmeth_rule_webmethods] CHECK CONSTRAINT [FK_logmeth_rule_webmethods_webmethods3]
GO
/****** Object:  ForeignKey [FK_notes_activity_inbox]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[notes]  WITH CHECK ADD  CONSTRAINT [FK_notes_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[notes] CHECK CONSTRAINT [FK_notes_activity_inbox]
GO
/****** Object:  ForeignKey [FK_notes_workflow_instances]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[notes]  WITH CHECK ADD  CONSTRAINT [FK_notes_workflow_instances] FOREIGN KEY([workflow_instance_id])
REFERENCES [dbo].[workflow_instances] ([workflow_instance_id])
GO
ALTER TABLE [dbo].[notes] CHECK CONSTRAINT [FK_notes_workflow_instances]
GO
/****** Object:  ForeignKey [R_211]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[notes]  WITH CHECK ADD  CONSTRAINT [R_211] FOREIGN KEY([created_by])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[notes] CHECK CONSTRAINT [R_211]
GO
/****** Object:  ForeignKey [FK_domain_vocabulary_datatypes]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[nouns]  WITH CHECK ADD  CONSTRAINT [FK_domain_vocabulary_datatypes] FOREIGN KEY([noun_datatype])
REFERENCES [dbo].[datatypes] ([datatype])
GO
ALTER TABLE [dbo].[nouns] CHECK CONSTRAINT [FK_domain_vocabulary_datatypes]
GO
/****** Object:  ForeignKey [FK_svcmethod_activity_svcmethods]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operation_log_ep]  WITH CHECK ADD  CONSTRAINT [FK_svcmethod_activity_svcmethods] FOREIGN KEY([operation_id])
REFERENCES [dbo].[operations] ([operation_id])
GO
ALTER TABLE [dbo].[operation_log_ep] CHECK CONSTRAINT [FK_svcmethod_activity_svcmethods]
GO
/****** Object:  ForeignKey [FK_operation_webmethod_operations]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operation_webmethod]  WITH CHECK ADD  CONSTRAINT [FK_operation_webmethod_operations] FOREIGN KEY([operation_id])
REFERENCES [dbo].[operations] ([operation_id])
GO
ALTER TABLE [dbo].[operation_webmethod] CHECK CONSTRAINT [FK_operation_webmethod_operations]
GO
/****** Object:  ForeignKey [FK_operation_webmethod_webmethods]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operation_webmethod]  WITH CHECK ADD  CONSTRAINT [FK_operation_webmethod_webmethods] FOREIGN KEY([webmethod_id])
REFERENCES [dbo].[webmethods] ([webmethod_id])
GO
ALTER TABLE [dbo].[operation_webmethod] CHECK CONSTRAINT [FK_operation_webmethod_webmethods]
GO
/****** Object:  ForeignKey [FK_operations_verbs]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[operations]  WITH CHECK ADD  CONSTRAINT [FK_operations_verbs] FOREIGN KEY([operation_verb_id])
REFERENCES [dbo].[verbs] ([verb_id])
GO
ALTER TABLE [dbo].[operations] CHECK CONSTRAINT [FK_operations_verbs]
GO
/****** Object:  ForeignKey [FK_org_participants_activity_inbox]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[org_participants]  WITH CHECK ADD  CONSTRAINT [FK_org_participants_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[org_participants] CHECK CONSTRAINT [FK_org_participants_activity_inbox]
GO
/****** Object:  ForeignKey [FK_org_participants_organization]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[org_participants]  WITH CHECK ADD  CONSTRAINT [FK_org_participants_organization] FOREIGN KEY([org_id])
REFERENCES [dbo].[organization] ([org_id])
GO
ALTER TABLE [dbo].[org_participants] CHECK CONSTRAINT [FK_org_participants_organization]
GO
/****** Object:  ForeignKey [FK_org_participants_participation_types]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[org_participants]  WITH CHECK ADD  CONSTRAINT [FK_org_participants_participation_types] FOREIGN KEY([participation_type_id])
REFERENCES [dbo].[participation_types] ([participation_type_id])
GO
ALTER TABLE [dbo].[org_participants] CHECK CONSTRAINT [FK_org_participants_participation_types]
GO
/****** Object:  ForeignKey [FK_org_unit_participants_activity_inbox]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[org_unit_participants]  WITH CHECK ADD  CONSTRAINT [FK_org_unit_participants_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[org_unit_participants] CHECK CONSTRAINT [FK_org_unit_participants_activity_inbox]
GO
/****** Object:  ForeignKey [FK_org_unit_participants_organization_unit]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[org_unit_participants]  WITH CHECK ADD  CONSTRAINT [FK_org_unit_participants_organization_unit] FOREIGN KEY([org_unit_id])
REFERENCES [dbo].[organization_unit] ([org_unit_id])
GO
ALTER TABLE [dbo].[org_unit_participants] CHECK CONSTRAINT [FK_org_unit_participants_organization_unit]
GO
/****** Object:  ForeignKey [FK_org_unit_participants_participation_types]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[org_unit_participants]  WITH CHECK ADD  CONSTRAINT [FK_org_unit_participants_participation_types] FOREIGN KEY([participation_type_id])
REFERENCES [dbo].[participation_types] ([participation_type_id])
GO
ALTER TABLE [dbo].[org_unit_participants] CHECK CONSTRAINT [FK_org_unit_participants_participation_types]
GO
/****** Object:  ForeignKey [R_141]    Script Date: 04/17/2009 14:02:33 ******/
ALTER TABLE [dbo].[organization]  WITH CHECK ADD  CONSTRAINT [R_141] FOREIGN KEY([lang_id])
REFERENCES [dbo].[language] ([lang_id])
GO
ALTER TABLE [dbo].[organization] CHECK CONSTRAINT [R_141]
GO
/****** Object:  ForeignKey [R_92]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[organization_unit]  WITH CHECK ADD  CONSTRAINT [R_92] FOREIGN KEY([org_id])
REFERENCES [dbo].[organization] ([org_id])
GO
ALTER TABLE [dbo].[organization_unit] CHECK CONSTRAINT [R_92]
GO
/****** Object:  ForeignKey [R_96]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[organization_unit_user_assoc]  WITH CHECK ADD  CONSTRAINT [R_96] FOREIGN KEY([org_unit_id])
REFERENCES [dbo].[organization_unit] ([org_unit_id])
GO
ALTER TABLE [dbo].[organization_unit_user_assoc] CHECK CONSTRAINT [R_96]
GO
/****** Object:  ForeignKey [R_97]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[organization_unit_user_assoc]  WITH CHECK ADD  CONSTRAINT [R_97] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[organization_unit_user_assoc] CHECK CONSTRAINT [R_97]
GO
/****** Object:  ForeignKey [FK_repository_diagrams_repository_types]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_diagrams]  WITH CHECK ADD  CONSTRAINT [FK_repository_diagrams_repository_types] FOREIGN KEY([diagram_type])
REFERENCES [dbo].[repository_types] ([reptype_id])
GO
ALTER TABLE [dbo].[repository_diagrams] CHECK CONSTRAINT [FK_repository_diagrams_repository_types]
GO
/****** Object:  ForeignKey [FK_repository_hierarchy_repositories]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_hierarchy]  WITH CHECK ADD  CONSTRAINT [FK_repository_hierarchy_repositories] FOREIGN KEY([repository_id])
REFERENCES [dbo].[repositories] ([repository_id])
GO
ALTER TABLE [dbo].[repository_hierarchy] CHECK CONSTRAINT [FK_repository_hierarchy_repositories]
GO
/****** Object:  ForeignKey [FK_repository_hierarchy_repository_types_child]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_hierarchy]  WITH CHECK ADD  CONSTRAINT [FK_repository_hierarchy_repository_types_child] FOREIGN KEY([child_reptype_id])
REFERENCES [dbo].[repository_types] ([reptype_id])
GO
ALTER TABLE [dbo].[repository_hierarchy] CHECK CONSTRAINT [FK_repository_hierarchy_repository_types_child]
GO
/****** Object:  ForeignKey [FK_repository_hierarchy_repository_types_parent]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[repository_hierarchy]  WITH CHECK ADD  CONSTRAINT [FK_repository_hierarchy_repository_types_parent] FOREIGN KEY([parent_reptype_id])
REFERENCES [dbo].[repository_types] ([reptype_id])
GO
ALTER TABLE [dbo].[repository_hierarchy] CHECK CONSTRAINT [FK_repository_hierarchy_repository_types_parent]
GO
/****** Object:  ForeignKey [R_98]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[role]  WITH CHECK ADD  CONSTRAINT [R_98] FOREIGN KEY([org_id])
REFERENCES [dbo].[organization] ([org_id])
GO
ALTER TABLE [dbo].[role] CHECK CONSTRAINT [R_98]
GO
/****** Object:  ForeignKey [FK_role_participants_activity_inbox]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[role_participants]  WITH CHECK ADD  CONSTRAINT [FK_role_participants_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[role_participants] CHECK CONSTRAINT [FK_role_participants_activity_inbox]
GO
/****** Object:  ForeignKey [FK_role_participants_participation_types]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[role_participants]  WITH CHECK ADD  CONSTRAINT [FK_role_participants_participation_types] FOREIGN KEY([participation_type_id])
REFERENCES [dbo].[participation_types] ([participation_type_id])
GO
ALTER TABLE [dbo].[role_participants] CHECK CONSTRAINT [FK_role_participants_participation_types]
GO
/****** Object:  ForeignKey [FK_role_participants_role]    Script Date: 04/17/2009 14:02:34 ******/
ALTER TABLE [dbo].[role_participants]  WITH CHECK ADD  CONSTRAINT [FK_role_participants_role] FOREIGN KEY([role_id])
REFERENCES [dbo].[role] ([role_id])
GO
ALTER TABLE [dbo].[role_participants] CHECK CONSTRAINT [FK_role_participants_role]
GO
/****** Object:  ForeignKey [FK_rule_constants_datatypes]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[rule_constants]  WITH CHECK ADD  CONSTRAINT [FK_rule_constants_datatypes] FOREIGN KEY([ruleconst_datatype])
REFERENCES [dbo].[datatypes] ([datatype])
GO
ALTER TABLE [dbo].[rule_constants] CHECK CONSTRAINT [FK_rule_constants_datatypes]
GO
/****** Object:  ForeignKey [FK_service_modules_capabilities]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[service_modules]  WITH CHECK ADD  CONSTRAINT [FK_service_modules_capabilities] FOREIGN KEY([capability_id])
REFERENCES [dbo].[capabilities] ([capability_id])
GO
ALTER TABLE [dbo].[service_modules] CHECK CONSTRAINT [FK_service_modules_capabilities]
GO
/****** Object:  ForeignKey [FK_service_modules_workflows]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[service_modules]  WITH CHECK ADD  CONSTRAINT [FK_service_modules_workflows] FOREIGN KEY([workflow_id])
REFERENCES [dbo].[workflows] ([workflow_id])
GO
ALTER TABLE [dbo].[service_modules] CHECK CONSTRAINT [FK_service_modules_workflows]
GO
/****** Object:  ForeignKey [FK_service_webservice_services]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[service_webservice]  WITH CHECK ADD  CONSTRAINT [FK_service_webservice_services] FOREIGN KEY([service_id])
REFERENCES [dbo].[services] ([service_id])
GO
ALTER TABLE [dbo].[service_webservice] CHECK CONSTRAINT [FK_service_webservice_services]
GO
/****** Object:  ForeignKey [FK_service_webservice_webservices]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[service_webservice]  WITH CHECK ADD  CONSTRAINT [FK_service_webservice_webservices] FOREIGN KEY([websvc_id])
REFERENCES [dbo].[webservices] ([websvc_id])
GO
ALTER TABLE [dbo].[service_webservice] CHECK CONSTRAINT [FK_service_webservice_webservices]
GO
/****** Object:  ForeignKey [FK_services_human_activities]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[services]  WITH CHECK ADD  CONSTRAINT [FK_services_human_activities] FOREIGN KEY([service_hact_id])
REFERENCES [dbo].[human_activities] ([hact_id])
GO
ALTER TABLE [dbo].[services] CHECK CONSTRAINT [FK_services_human_activities]
GO
/****** Object:  ForeignKey [FK_sobas_workflows]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[sobas]  WITH CHECK ADD  CONSTRAINT [FK_sobas_workflows] FOREIGN KEY([workflow_id])
REFERENCES [dbo].[workflows] ([workflow_id])
GO
ALTER TABLE [dbo].[sobas] CHECK CONSTRAINT [FK_sobas_workflows]
GO
/****** Object:  ForeignKey [FK_user_login_log_users]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[user_login_log]  WITH CHECK ADD  CONSTRAINT [FK_user_login_log_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[user_login_log] CHECK CONSTRAINT [FK_user_login_log_users]
GO
/****** Object:  ForeignKey [FK_user_participants_activity_inbox]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[user_participants]  WITH CHECK ADD  CONSTRAINT [FK_user_participants_activity_inbox] FOREIGN KEY([activity_id])
REFERENCES [dbo].[activity_inbox] ([activity_id])
GO
ALTER TABLE [dbo].[user_participants] CHECK CONSTRAINT [FK_user_participants_activity_inbox]
GO
/****** Object:  ForeignKey [FK_user_participants_participation_types]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[user_participants]  WITH CHECK ADD  CONSTRAINT [FK_user_participants_participation_types] FOREIGN KEY([participation_type_id])
REFERENCES [dbo].[participation_types] ([participation_type_id])
GO
ALTER TABLE [dbo].[user_participants] CHECK CONSTRAINT [FK_user_participants_participation_types]
GO
/****** Object:  ForeignKey [R_118]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[user_role_assoc]  WITH CHECK ADD  CONSTRAINT [R_118] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[user_role_assoc] CHECK CONSTRAINT [R_118]
GO
/****** Object:  ForeignKey [R_20]    Script Date: 04/17/2009 14:02:35 ******/
ALTER TABLE [dbo].[user_role_assoc]  WITH CHECK ADD  CONSTRAINT [R_20] FOREIGN KEY([role_id])
REFERENCES [dbo].[role] ([role_id])
GO
ALTER TABLE [dbo].[user_role_assoc] CHECK CONSTRAINT [R_20]
GO
/****** Object:  ForeignKey [R_10]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[users]  WITH CHECK ADD  CONSTRAINT [R_10] FOREIGN KEY([org_id])
REFERENCES [dbo].[organization] ([org_id])
GO
ALTER TABLE [dbo].[users] CHECK CONSTRAINT [R_10]
GO
/****** Object:  ForeignKey [R_142]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[users]  WITH CHECK ADD  CONSTRAINT [R_142] FOREIGN KEY([lang_id])
REFERENCES [dbo].[language] ([lang_id])
GO
ALTER TABLE [dbo].[users] CHECK CONSTRAINT [R_142]
GO
/****** Object:  ForeignKey [FK_verbs_capabilites]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[verbs]  WITH CHECK ADD  CONSTRAINT [FK_verbs_capabilites] FOREIGN KEY([capability_id])
REFERENCES [dbo].[capabilities] ([capability_id])
GO
ALTER TABLE [dbo].[verbs] CHECK CONSTRAINT [FK_verbs_capabilites]
GO
/****** Object:  ForeignKey [FK_verbs_logical_webmethods]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[verbs]  WITH CHECK ADD  CONSTRAINT [FK_verbs_logical_webmethods] FOREIGN KEY([log_wmeth_id])
REFERENCES [dbo].[logical_webmethods] ([log_wmeth_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[verbs] CHECK CONSTRAINT [FK_verbs_logical_webmethods]
GO
/****** Object:  ForeignKey [FK_webmethods_webservices]    Script Date: 04/17/2009 14:02:36 ******/
ALTER TABLE [dbo].[webmethods]  WITH CHECK ADD  CONSTRAINT [FK_webmethods_webservices] FOREIGN KEY([websvc_id])
REFERENCES [dbo].[webservices] ([websvc_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[webmethods] CHECK CONSTRAINT [FK_webmethods_webservices]
GO
/****** Object:  ForeignKey [FK_wf_events_business_objects1]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_events]  WITH CHECK ADD  CONSTRAINT [FK_wf_events_business_objects1] FOREIGN KEY([wf_event_bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[wf_events] CHECK CONSTRAINT [FK_wf_events_business_objects1]
GO
/****** Object:  ForeignKey [FK_wf_events_workflows]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_events]  WITH CHECK ADD  CONSTRAINT [FK_wf_events_workflows] FOREIGN KEY([workflow_id])
REFERENCES [dbo].[workflows] ([workflow_id])
GO
ALTER TABLE [dbo].[wf_events] CHECK CONSTRAINT [FK_wf_events_workflows]
GO
/****** Object:  ForeignKey [FK_wf_fields_business_objects]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_fields]  WITH CHECK ADD  CONSTRAINT [FK_wf_fields_business_objects] FOREIGN KEY([wf_field_bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[wf_fields] CHECK CONSTRAINT [FK_wf_fields_business_objects]
GO
/****** Object:  ForeignKey [FK_wf_fields_workflows]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_fields]  WITH CHECK ADD  CONSTRAINT [FK_wf_fields_workflows] FOREIGN KEY([workflow_id])
REFERENCES [dbo].[workflows] ([workflow_id])
GO
ALTER TABLE [dbo].[wf_fields] CHECK CONSTRAINT [FK_wf_fields_workflows]
GO
/****** Object:  ForeignKey [FK_wf_methods_business_objects2]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_methods]  WITH CHECK ADD  CONSTRAINT [FK_wf_methods_business_objects2] FOREIGN KEY([wf_method_input_bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[wf_methods] CHECK CONSTRAINT [FK_wf_methods_business_objects2]
GO
/****** Object:  ForeignKey [FK_wf_methods_business_objects3]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_methods]  WITH CHECK ADD  CONSTRAINT [FK_wf_methods_business_objects3] FOREIGN KEY([wf_method_output_bobj_id])
REFERENCES [dbo].[business_objects] ([bobj_id])
GO
ALTER TABLE [dbo].[wf_methods] CHECK CONSTRAINT [FK_wf_methods_business_objects3]
GO
/****** Object:  ForeignKey [FK_wf_methods_workflows]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[wf_methods]  WITH CHECK ADD  CONSTRAINT [FK_wf_methods_workflows] FOREIGN KEY([workflow_id])
REFERENCES [dbo].[workflows] ([workflow_id])
GO
ALTER TABLE [dbo].[wf_methods] CHECK CONSTRAINT [FK_wf_methods_workflows]
GO
/****** Object:  ForeignKey [FK_workflow_brules_repository_business_rules]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[workflow_brules]  WITH CHECK ADD  CONSTRAINT [FK_workflow_brules_repository_business_rules] FOREIGN KEY([brule_id])
REFERENCES [dbo].[repository_business_rules] ([brule_id])
GO
ALTER TABLE [dbo].[workflow_brules] CHECK CONSTRAINT [FK_workflow_brules_repository_business_rules]
GO
/****** Object:  ForeignKey [FK_workflow_brules_workflows]    Script Date: 04/17/2009 14:02:37 ******/
ALTER TABLE [dbo].[workflow_brules]  WITH CHECK ADD  CONSTRAINT [FK_workflow_brules_workflows] FOREIGN KEY([workflow_id])
REFERENCES [dbo].[workflows] ([workflow_id])
GO
ALTER TABLE [dbo].[workflow_brules] CHECK CONSTRAINT [FK_workflow_brules_workflows]
GO
/****** Object:  ForeignKey [FK_workflows_domain_configuration_paths]    Script Date: 04/17/2009 14:02:38 ******/
ALTER TABLE [dbo].[workflows]  WITH CHECK ADD  CONSTRAINT [FK_workflows_domain_configuration_paths] FOREIGN KEY([domain_config_path_id])
REFERENCES [dbo].[domain_configuration_paths] ([config_path_id])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[workflows] CHECK CONSTRAINT [FK_workflows_domain_configuration_paths]
GO
/****** Object:  ForeignKey [FK_workflows_webservices]    Script Date: 04/17/2009 14:02:38 ******/
ALTER TABLE [dbo].[workflows]  WITH CHECK ADD  CONSTRAINT [FK_workflows_webservices] FOREIGN KEY([workflow_websvc_id])
REFERENCES [dbo].[webservices] ([websvc_id])
GO
ALTER TABLE [dbo].[workflows] CHECK CONSTRAINT [FK_workflows_webservices]
GO
